0

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!

2
Contributors
3
Replies
8
Views
9 Years
Discussion Span
Last Post by Sulley's Boo
0
select ID, date, time from tbl
where date =(select max(date) from tbl)
and time= (select max(time) from tbl)
group by ID

?

0

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!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.