[sql] SQL get the last date time record

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

Try this:

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

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

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

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.

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

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

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