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!

Recommended Answers

select ID, date, time from tbl
where date =(select max(date) from tbl)
and time= (select max(time) from tbl)
group by ID

?

Jump to Post

All 3 Replies

select ID, date, time from tbl
where date =(select max(date) from tbl)
and time= (select max(time) from tbl)
group by ID

?

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!

thowwy!!!! >_<

select id, date1, max(timee) from datetime 
group by id, date1

This should work ..

+_+

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.