SQL get the last date time record [duplicate]

Question

This question already has an answer here:

• SQL Server: SELECT only the rows with MAX(DATE) 9 answers

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

Answer 1

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

Answer 2

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/#!3/c94a2/2

HTH

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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