October 29, 2008 at 4:58 pm
Im stumped and I KNOW this is a simple query. Lets say I have this .
select column list
from tablename
where datetime column = (select max(datetime column) from tablename);
Which correctly returns the record with the most recent date. Fine. BUT, what if 2 records are created on the same date, how would I say "if 2 records have the same date, then return the one with the most recent time?"
Thanks!
October 29, 2008 at 6:50 pm
If it's trully a DateTime column, then MAX will flush out the one with the latest time "auto-magically".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2008 at 6:51 pm
That is, of course, if you don't have the time in a separate column... let us know.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2008 at 8:44 am
Time is in a different column. Thats why I cant figure out how to do it.
October 30, 2008 at 9:00 am
could you take a different approach and do something like this:
select TOP 1 column list
from tablename
order by datecolumn DESC, timecolumn DESC
I suppose it depends on how this query is being used, and what you expect to happen if there are 2 records with the exact same date and time. If you need to handle that case, maybe something like:
select column list
from
(select column list, RANK() OVER (ORDER BY datecolumn DESC, timecolumn DESC) as ranking
from tablename) sq
where ranking = 1
October 30, 2008 at 9:16 am
Chris Harshman (10/30/2008)
could you take a different approach and do something like this:
select TOP 1 column list
from tablename
order by datecolumn DESC, timecolumn DESC
I suppose it depends on how this query is being used, and what you expect to happen if there are 2 records with the exact same date and time. If you need to handle that case, maybe something like:
select column list
from
(select column list, RANK() OVER (ORDER BY datecolumn DESC, timecolumn DESC) as ranking
from tablename) sq
where ranking = 1
Wow! So simple, yet I couldnt figure out the logic. But this will work fine for my purposeses. The second one I need to chew on a bit because im not familiar with the rank() operator. In my case though there will never be identical date and time for a record, so I believe your first solution is simple and perfect. Although I am going to be using that logic as part of a join condition, will the order by statements still work?
^^
October 30, 2008 at 9:18 am
If you need to join the results of this query to something else, it may be easiest to set it up as a derived table (a subquery in your FROM clause).
October 30, 2008 at 9:21 am
Something like this?
select *
from (select TOP 1 column list
from tablename
order by datecolumn DESC, timecolumn DESC) as derived table
October 30, 2008 at 9:34 am
yes, if I'm understanding you right. To be more specific:
select
from (select top 1 column1, column2, column3, column4
from tablename
order by datecolumn DESC, timecolumn DESC) as dt
inner join someothertable sot on dt.column1 = sot.column2
October 30, 2008 at 9:36 am
Chris Harshman (10/30/2008)
yes, if I'm understanding you right. To be more specific:
select
from (select top 1 column1, column2, column3, column4
from tablename
order by datecolumn DESC, timecolumn DESC) as dt
inner join someothertable sot on dt.column1 = sot.column2
Yep, thats exactly what I am going for. I appreciate the help! Sometimes I tend to overthink things.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply