Return most recent date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Return most recent date

Post 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?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Do as you think - order by the date and then limit to x number of results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post by primate »

er thanks feyd 8O ....what?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

select top 1 from TableName order by datefield desc
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
Last edited by John Cartwright on Wed Oct 20, 2004 6:12 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
primate
Forum Commoner
Posts: 49
Joined: Fri Jun 18, 2004 4:38 am
Location: England, UK

Post 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 :)
polyhymnia
Forum Newbie
Posts: 2
Joined: Wed Oct 27, 2004 8:34 am
Location: Scotland

Group by NULL

Post 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
polyhymnia
Forum Newbie
Posts: 2
Joined: Wed Oct 27, 2004 8:34 am
Location: Scotland

ignore that last one

Post 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.
Post Reply