[thelist] SQL Max Query Distinct

Chris Blessing webguy at mail.rit.edu
Thu Nov 14 13:13:16 CST 2002


Josh-

All you have to do is group by the userid.  You can also get a count for the
number of records per userid also by including a count(id) in the select
clause.  Try this:

select max(Date), id
from my_table
group by id

and...

select max(Date), id, count(id)
from my_table
group by id

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> [ Picked text/plain from multipart/alternative ]
> I have some data that looks like this:
> Date                                  ID
> 05-01-2001 00:00:00.000 63
> 06-01-2001 00:00:00.000 63
> 07-01-2001 00:00:00.000 63
> 05-01-2001 00:00:00.000 71
> 06-01-2001 00:00:00.000 71
> 07-01-2001 00:00:00.000 71
> 08-01-2001 00:00:00.000 71
> 05-01-2001 00:00:00.000 85
> 06-01-2001 00:00:00.000 85
> 06-01-2001 00:00:00.000 99
>
> I want to return a recordset that has the LATEST date for EACH ID.  Can
> anyone help me with the SQL.
>
> Thanks,
> Josh




More information about the thelist mailing list