SQL get the last date time record

Question

I'm trying to get the last datetime record from a table that happens to store multiple status. My table looks like so:

+---------+------------------------+-------+
|filename |Dates                   |Status |
+---------+------------------------+-------+
|abc.txt  |2012-02-14 12:04:45.397 |Open   |
|abc.txt  |2012-02-14 12:14:20.997 |Closed |
|abc.txt  |2013-02-14 12:20:59.407 |Open   |
|dfg.txt  |2012-02-14 12:14:20.997 |Closed |
|dfg.txt  |2013-02-14 12:20:59.407 |Open   |
+---------+------------------------+-------+

The results should be

+---------+------------------------+-------+
|filename |Dates                   |Status |
+---------+------------------------+-------+
|abc.txt  |2013-02-14 12:20:59.407 |Open   |
|dfg.txt  |2013-02-14 12:20:59.407 |Open   |
+---------+------------------------+-------+

score 50 · Accepted Answer · answered May 14 '13 at 20:07

50

If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend:

select filename ,
       status   ,
       max_date = max( dates )
from some_table t
group by filename , status
having status = '<your-desired-status-here>'

Easy!

answered May 14 '13 at 20:07

Nicholas Carey

71,308
16
93
135

2

Any comments on performance? Say he has many millions of records would there be an optimized way of doing this, or should he consider putting it into a new table of latest records? – JPK Feb 20 '15 at 11:58
It all depends on the indices on the table. You have to examine the execution plan. As a general rule: don't optimize until you have a problem. You could put an insert/update/delete trigger on the table that would maintain a summary table with the most recent date for each file/status combination. However, that has performance implications: each insert/update/delete operation on the detail table has to modify the summary table. It also increases contention in the DB (since you're now locking two tables instead of just one). It also means you now have two sources of truth in the database. – Nicholas Carey Feb 20 '15 at 19:17
ORA-00923: FROM keyword not found where expected – Zizzipupp Jan 06 '20 at 12:48

vidit · Answer 2 · 2013-05-14T19:10:34.310

36

SELECT * FROM table
WHERE Dates IN (SELECT max(Dates) FROM table);

edited May 14 '13 at 19:10

answered May 14 '13 at 18:59

vidit

6,293
3
32
50

1

How does that take into account the multiple statuses part of the question? – wootscootinboogie May 14 '13 at 19:00
It doesn't, it gets all the entries matching max date. – vidit May 14 '13 at 19:01
Here is a demo -- http://sqlfiddle.com/#!3/c94a2/1 – Taryn May 14 '13 at 19:02
Hmm for some reason this is returning only one record. Could this be because I'm running on a bit older sql (sql 2000) – Miguel May 14 '13 at 19:16
@Miguel- Do you want to get last date entry for all files? Or all entries with the latest date? – vidit May 14 '13 at 19:17
I would like to get every file entered with the latest date. – Miguel May 14 '13 at 19:20
@Miguel- then the above query should work, you can check the demo link posted above by **bluefeet** – vidit May 14 '13 at 19:24

score 23 · Answer 3 · edited Nov 14 '18 at 18:59

23

SELECT TOP 1 * FROM foo ORDER BY Dates DESC

Will return one result with the latest date.

SELECT * FROM foo WHERE foo.Dates = (SELECT MAX(Dates) FROM foo)

Will return all results that have the same maximum date, to the milissecond.

This is for SQL Server. I'll leave it up to you to use the DATEPART function if you want to use dates but not times.

edited Nov 14 '18 at 18:59

T. Piscaglia

50
1
5

answered May 14 '13 at 19:01

Geeky Guy

9,229
4
42
62

1

This is the only one that worked for me – Zizzipupp Jan 06 '20 at 12:52

score 7 · Answer 4 · edited Mar 14 '16 at 12:25

7

this working

SELECT distinct filename
,last_value(dates)over (PARTITION BY filename ORDER BY filename)posd
,last_value(status)over (PARTITION BY filename ORDER BY filename )poss
FROM distemp.dbo.Shmy_table

edited Mar 14 '16 at 12:25

Prafulla Kumar Sahu

9,321
11
68
105

answered Mar 14 '16 at 11:37

shikos

71
1
1

This is function is awesome, you can create recursive loops to fill empty data. – Rafa Barragan Jul 11 '17 at 00:09
Since SQL Server 2012: https://learn.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql – Vadzim Nov 02 '17 at 17:51
It's preferent to use FIRTS_VALUE function with the ORDER BY sort order you need. Because LAST_VALUE function need to be specificaly window frame, if not it can lead you to wrong results. – Tanner Ornelas May 25 '19 at 19:45

Osy · Answer 5 · 2020-01-16T14:14:23.717

5

Considering that max(dates) can be different for each filename, my solution :

select filename, dates, status
from yt a
where a.dates = (
  select max(dates)
    from yt b
    where a.filename = b.filename
)
;

http://sqlfiddle.com/#!18/fdf8d/1/0

HTH

edited Jan 16 '20 at 14:14

answered May 14 '13 at 19:23

Osy

1,613
5
21
35

Duffmaster33 · Answer 6 · 2013-05-14T19:07:40.890

2

Exact syntax will of course depend upon database, but something like:

SELECT * FROM my_table WHERE (filename, Dates) IN (SELECT filename, Max(Dates) FROM my_table GROUP BY filename)

This will give you results exactly what you are asking for and displaying above. Fiddle: http://www.sqlfiddle.com/#!2/3af8a/1/0

edited May 14 '13 at 19:07

answered May 14 '13 at 19:00

Duffmaster33

1,160
9
16

score 0 · Answer 7 · answered May 14 '13 at 18:59

0

select max(dates)
from yourTable
group by dates
having count(status) > 1

answered May 14 '13 at 18:59

wootscootinboogie

8,461
33
112
197

score -3 · Answer 8 · edited Jul 27 '15 at 14:15

-3

Try this:

SELECT filename,Dates,Status 
FROM TableName 
WHERE Dates In (SELECT MAX(Dates) FROM TableName GROUP BY filename)

edited Jul 27 '15 at 14:15

duffy356

3,678
3
32
47

answered Jul 27 '15 at 13:45

wara

31
3

This may return extra old dates for some filenames. – Vadzim Nov 02 '17 at 17:55
How , please explain . – wara Nov 06 '17 at 09:07
@wara if fileA has an old status with a date that matches the latest status of fileB, then you would see that old fileA status in the result (and the latest fileA status too). – AnorZaken Oct 21 '20 at 07:41
I get you I assumed based on the dataset he provided where he uses a timestamp so there are less chances that Old and New file can have the same timestamp. But I understand your theory in basic – wara Oct 22 '20 at 08:46

SQL get the last date time record

8 Answers8

Linked