Select max date, then max time for that date?
 

Hey guys

I have a table, that lists people, with a correpsonding date and time for each activity. My structure is like:

PersonID......Date............Time
872367.........01/01/08......15:00:00
872367.........01/01/08......16:00:00
872367.........01/01/08......18:00:00
976737.........01/01/08......19:00:00
976737.........02/01/08......16:00:00
911967.........03/01/08......18:00:00

I want to have a query that returns each persons latest activity date and time, so I want a table like:

PersonID......Date............Time
872367.........01/01/08......18:00:00
976737.........02/01/08......16:00:00
911967.........03/01/08......18:00:00

I tried something like

select PersonID, max(Date), max(Time)

from myTable

group by PersonID

This returned the

PersonID, latest activity date for that person, but not the latest time for that date, just the latest time for that person, if that makes sense?

So I got something like:

PersonID......Date............Time
872367.........01/01/08......18:00:00
976737.........02/01/08......19:00:00
911967.........03/01/08......18:00:00

Notice how the second row of data has the max date for that person, and the max time for that person - I need the max date for that person, and the max time for that date

Thanks for any help!

Re: Select max date, then max time for that date?
 

select ID, date, time from tbl

where date =(select max(date) from tbl)

and time= (select max(time) from tbl)

group by ID

?

Re: Select max date, then max time for that date?
 

that wouldn't run, saying it wasnt a group by, so i added the date and time to the group by, and when i ran it, i didnt get any results back, just an empty result set

thanks for your time!

Re: Select max date, then max time for that date?
 

thowwy!!!! >_<

select id, date1, max(timee) from datetime 

group by id, date1

This should work ..

+_+