Get last time from datetime field

[bigdavelamb]

Hi, i have a table of logged transactions with a datetime field and a userid, each day can have any number of transactions by a user but with different datetimes, how i can get the last time for each day by user, here is a sample of the data:

logid userid datetime
285439 5 2004-10-05 10:06:16.000
285440 61 2004-11-05 10:06:19.000
285441 61 2004-11-05 10:06:31.000
285442 5 2004-11-05 10:06:34.000
285443 928 2004-11-05 10:06:36.000
285444 5 2004-12-05 10:06:47.000


I have messed about with MAX() and groupings but seem to not have cracked it, thanks alot!

Dave

 

[MattiasJ]

This is a way to address your problem:


select
convert(char(8), Mydate, 112) as MyDate2,
MyID,
Max(MyDate)
from
MyTable
group by
convert(char(8), Mydate, 112),
MyID


Regards
/Mattias

 

[bigdavelamb]

Thank you Mattias - much appreciated.

dave