Just need each 1 rec for each client, the most recent date 1

[ljsmith91]

I have a simple SQL table that contains only a few fields. One field is "client", another is "my_date". There are a few other fields I need to pull too. But since there are so many dates for each client, I only want to output the most recent date for each client. I have tried both TOP and MAX(my_date) and I cannot seem to get it to do what I need. "my_date" is a datetime field. It still outputs everything. Do I need some sort of subquery ? As you can tell, I am no expert. Thanks for any help. -ljs

SELECT client, job, MAX(my_date), kbytes, filecount
FROM jobs_daily_summary
GROUP BY
client, job, kbytes, filecount
ORDER BY
client

 

[RiverGuy]

SELECT a.*
FROM jobs_daily_summary a
INNER JOIN 
  (SELECT Client, MAX(my_date) AS MaxDate
  FROM jobs_daily_summary
  GROUP BY Client) b
ON a.client = b.client AND a.my_date = b.MaxDate

 

[ljsmith91]

RiverGuy,

That did it. Thanks so much.

-ljs