SQL get the last date time record [duplicate]

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

Solution 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!

Solution 2:

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

Solution 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.