SQL获取最后一个日期时间记录[重复]

This question already has an answer here:

这个问题在这里已有答案：

SQL Server: SELECT only the rows with MAX(DATE) 9 answers
SQL Server：仅选择MAX（DATE）9个答案的行

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

8 个解决方案

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

简单！

#2

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

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

将所有具有相同最大日期的结果返回到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.

这适用于SQL Server。如果你想使用日期而不是时间，我会留给你使用DATEPART功能。

#4

Considering that max(dates) can be different for each filename, my solution :

考虑到每个文件名的max（日期）可能不同，我的解决方案：

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

#5

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

#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

这将为您提供您要求和上面显示的结果。小提琴：http：//www.sqlfiddle.com/#！2/3af8a/1/0

#7

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

#8

-2

Try this:

尝试这个：

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

#1