Select most recent record based on date

Question

I'm trying to create a select query that returns the most recent record from the table based on date. Basically whichever row's [Date] field is closest to the current date.

Sample Data:

    ID       Content         Date
--------------------------------------------
1   1050    Test Values    2013-11-27 10:46:24.900
2   1051    Test Test      2013-11-27 10:47:43.150
3   1057    Test Testx2    2013-11-27 10:48:22.820

I would like to only return these values

    ID       Content         Date
--------------------------------------------
1   1057    Test Testx2    2013-11-27 10:48:22.820

Thanks!

Date is not a valid name for a column; it is a type name. (SQL is not case sensitive) — wildplasser
– wildplasser, Commented Nov 27, 2013 at 15:58
@wildplasser I just used [Date] as an example. My actual column name is [Timestamp]. — Justin Adkins
– Justin Adkins, Commented Nov 27, 2013 at 16:00

Christos · Accepted Answer · 2018-09-16 20:53:22Z

6

You could try the following query:

SELECT TOP 1 *
FROM Table
ORDER BY [Date] DESC

edited Sep 16, 2018 at 20:53

answered Nov 27, 2013 at 15:55

Christos

54.1k9 gold badges82 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thorsten Dittmar Over a year ago

No, this gives the oldest entry and won't work as you're not specifying the fields you want to select or *.

Christos Over a year ago

@Thorten Thanks! It took me few seconds to check it in management studio, because I didn't remember the syntax of TOP

Justin Adkins Over a year ago

Yep, this worked. I simply changed * to the fields that I needed.

Yaroslav Over a year ago

use brackets for date, is a reserved word in SQL Server 2008+

dg99 · Accepted Answer · 2013-11-27 16:02:50Z

3

Or, if you want your query to work on any DBMS (not just SQL Server), use ANSI SQL:

select * from t order by Date desc limit 1

or

select * from t where Date = (select max(Date) from t)

answered Nov 27, 2013 at 16:02

dg99

5,7383 gold badges40 silver badges51 bronze badges

Comments

Thorsten Dittmar · Accepted Answer · 2013-11-27 16:01:31Z

1

SELECT TOP 1 * FROM Table ORDER BY [Date] DESC

This returns all fields from the first record after sorting from the highest date to the lowest. Essentially this returns the newest entry.

edited Nov 27, 2013 at 16:01

answered Nov 27, 2013 at 15:56

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Comments

user474901 · Accepted Answer · 2013-11-27 15:58:29Z

0

SELECT TOP 1  ID,Content,Date FROM <TABLE_NAME> ORDER BY Date

answered Nov 27, 2013 at 15:58

user474901

Collectives™ on Stack Overflow

Select most recent record based on date

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related