Oracle

Select max date, then max time for that date?

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2008

Posts: 33

Reputation: 2eXtreme is an unknown quantity at this point

Solved Threads: 0

2eXtreme

Offline

Light Poster

Select max date, then max time for that date?

Jul 3rd, 2008

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

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
SELECT PersonID, MAX(DATE), MAX(TIME)
FROM myTable
GROUP BY PersonID
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!

•

Join Date: Dec 2004

Posts: 440

Reputation: Sulley's Boo will become famous soon enough

Solved Threads: 10

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

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

Jul 3rd, 2008

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
SELECT ID, DATE, TIME FROM tbl
WHERE DATE =(SELECT max(DATE) FROM tbl)
AND TIME= (SELECT max(TIME) FROM tbl)
GROUP BY ID
select ID, date, time from tbl
where date =(select max(date) from tbl)
and time= (select max(time) from tbl)
group by ID

•

Join Date: May 2008

Posts: 33

Reputation: 2eXtreme is an unknown quantity at this point

Solved Threads: 0

2eXtreme

Offline

Light Poster

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

Jul 3rd, 2008

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!

•

Join Date: Dec 2004

Posts: 440

Reputation: Sulley's Boo will become famous soon enough

Solved Threads: 10

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

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

Jul 6th, 2008

thowwy!!!! >_<

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT id, date1, max(timee) FROM DATETIME 
GROUP BY id, date1
select id, date1, max(timee) from datetime 
group by id, date1