Page 1 of 1

Return most recent date

Posted: Mon Oct 18, 2004 10:33 am
by primate
I am trying to figure out a way to select a row of data based on it having the most recent date compared to other records. Its an MS SQL database.

Is there any straight forward SQL syntax for doing this or will I have to do something like order by then select the first result?

Posted: Mon Oct 18, 2004 10:35 am
by kettle_drum
Do as you think - order by the date and then limit to x number of results.

Posted: Mon Oct 18, 2004 10:49 am
by feyd
selecting the closest date based on a random record can be done through joining. Although I don't know if MS SQL server supports those requests. In the past, I've had the sql find the closest date via taking the absolute value of the record being compared to and the other records in the table, finding the smallest difference.

Posted: Wed Oct 20, 2004 3:47 am
by primate
er thanks feyd 8O ....what?

Posted: Wed Oct 20, 2004 8:34 am
by feyd
search through past posts:

viewtopic.php?t=26782 <-- the logic of what I talked about.
viewtopic.php?t=26423 <-- what I remember posting about.

Posted: Wed Oct 20, 2004 3:21 pm
by lostboy
select top 1 from TableName order by datefield desc

Posted: Wed Oct 20, 2004 3:36 pm
by John Cartwright
lostboy wrote:select top 1 from TableName order by datefield desc
That wil only get the newest row.. what he needs if the closest record to a specific date

Posted: Wed Oct 20, 2004 5:38 pm
by timvw
ah finding the smallest interval (pair of timestamps) from a set of timestamps...

you can bruteforce it as suggested int he previously mentionned links....
OR you can use *math*, lookup "closest-pair problem algorithm" :)

You can find a nice explanation here:
http://www.cs.ucsb.edu/~suri/cs235/ClosestPair.pdf

As you see, your problem is also 1 dimensional.... so it should be easily translated from x-coordinates to timestamps...

Posted: Wed Oct 20, 2004 6:00 pm
by lostboy
my bad. misunderstood recent time to be the latest one...

he can join the table to itself or use a subquery to limit it...guess it depends on wha the time period is

Posted: Thu Oct 21, 2004 4:56 am
by primate
lostboy wrote:my bad. misunderstood recent time to be the latest one...
No you didn't, that was what I meant, and your suggestion is what I've done.

Thanks for eveyone's help :)

Group by NULL

Posted: Wed Oct 27, 2004 9:03 am
by polyhymnia
Using mySQL, I can get just the line from the user with the latest endtime using:

Code: Select all

select user_id, startime, max(endtime) from cycle_study group by NULL;
So I guess generically, it would be:

Code: Select all

select desired column, max(date) from table group by NULL;
-Lisa

ignore that last one

Posted: Mon Dec 13, 2004 2:10 pm
by polyhymnia
Oops, ignore what I said last. The username displayed with MAX(datetime) when grouping by NULL has no relation to the one on the same line as the maximum datetime.