SQL获取最后的日期时间记录

Question

这个问题已经有答案了:

SQL Server: SELECT only the rows with MAX(DATE) (12 个回答)

已关闭 5 年前。

社区 2 年前审查了是否重新开放此问题，并将其关闭:

原始关闭原因未解决

我正在尝试从恰好存储多个状态的表中获取最后一个日期时间记录。我的 table 看起来像这样:

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

结果应该是

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

如果您希望每个文件名占一行，反射(reflect)特定状态并列出最近的日期，那么这就是您的 friend :

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

简单!