SQL get the last date time record [duplicate]

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   |
+---------+------------------------+-------+

Glazed

Answer 1

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!

Bred

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? – Denounce

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. – Bred

ORA-00923: FROM keyword not found where expected – Elene

Answer 2

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

Nitrogen

How does that take into account the multiple statuses part of the question? – Ruhl

It doesn't, it gets all the entries matching max date. – Nitrogen

Here is a demo -- sqlfiddle.com/#!3/c94a2/1 – Leede

Hmm for some reason this is returning only one record. Could this be because I'm running on a bit older sql (sql 2000) – Glazed

@Miguel- Do you want to get last date entry for all files? Or all entries with the latest date? – Nitrogen

I would like to get every file entered with the latest date. – Glazed

@Miguel- then the above query should work, you can check the demo link posted above by bluefeet – Nitrogen

Answer 3

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.

Porte

This is the only one that worked for me – Elene

Answer 4

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

Xyster

This is function is awesome, you can create recursive loops to fill empty data. – Vannavannatta

Since SQL Server 2012: learn.microsoft.com/en-us/sql/t-sql/functions/… – Preoccupation

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. – Deckert

Answer 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

Acetophenetidin

Answer 6

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

Root

Answer 7

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

Ruhl

Answer 8

Try this:

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

Doublehung

This may return extra old dates for some filenames. – Preoccupation

How , please explain . – Doublehung

@Doublehung 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). – Querida

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 – Doublehung