Return most recent date
Moderator: General Moderators
Return most recent date
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?
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?
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
search through past posts:
viewtopic.php?t=26782 <-- the logic of what I talked about.
viewtopic.php?t=26423 <-- what I remember posting about.
viewtopic.php?t=26782 <-- the logic of what I talked about.
viewtopic.php?t=26423 <-- what I remember posting about.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
That wil only get the newest row.. what he needs if the closest record to a specific datelostboy wrote:select top 1 from TableName order by datefield desc
Last edited by John Cartwright on Wed Oct 20, 2004 6:12 pm, edited 1 time in total.
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...
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...
-
polyhymnia
- Forum Newbie
- Posts: 2
- Joined: Wed Oct 27, 2004 8:34 am
- Location: Scotland
Group by NULL
Using mySQL, I can get just the line from the user with the latest endtime using:
So I guess generically, it would be:
-Lisa
Code: Select all
select user_id, startime, max(endtime) from cycle_study group by NULL;Code: Select all
select desired column, max(date) from table group by NULL;-
polyhymnia
- Forum Newbie
- Posts: 2
- Joined: Wed Oct 27, 2004 8:34 am
- Location: Scotland
ignore that last one
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.