SQL Duplicate Rows 1

[tcgoth]

I am using the following SQL Query:

select distinct cust_org_name AS Client, cust_numb AS File_#, cust_lname+ ', '+cust_fname AS EE_Name, max(dbo.form_0007_listing.dt_list_date) AS original_list_date 
from dbo.rpt_customer inner join dbo.form_0007_listing ON dbo.rpt_customer.cust_uid = dbo.form_0007_listing.cust_uid  
where cust_org_name like '%usda%'
group by dbo.form_0007_listing.dt_list_date,cust_org_name,cust_numb,cust_lname,cust_fname
order by ee_name

I continue to get multiple records in the dbo.form_0007_listing.dt_list_date field even though I have attempted to get the "Max" value. I don't think the Group By is correct either but this is the only way I could get the query to run without errors. How do I restrict dbo.form_0007_listing.dt_list_date to the latest date value, and only one row per record?


Here is the output with dt_list_date to the far right:

Client 3486 XXXXXXX, Tom 2004-03-10 00:00:00.000
Client 3486 XXXXXXX, Tom 2004-04-23 00:00:00.000

 

[amrita418]

Remove the dt_list_date from the group by clause and see what you get.

Regards,
AA