Select max date, then max time for that date?

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 391,989 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,217 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Oracle advertiser:

Thread Tools Display Modes

Views: 1264 | Replies: 3

•

Join Date: May 2008

Posts: 33

Reputation: 2eXtreme is an unknown quantity at this point

Rep Power: 1

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

(Toggle Plain Text)

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: 406

Reputation: Sulley's Boo will become famous soon enough

Rep Power: 5

Solved Threads: 9

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

Rep Power: 1

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: 406

Reputation: Sulley's Boo will become famous soon enough

Rep Power: 5

Solved Threads: 9

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