从历史记录表中选择最新状态

I have inherited a table with a structure something like this:

我继承了一个具有如下结构的表:

ID   Name   Timestamp   Data
----------------------------
1    A      40          ...
2    A      30          ...
3    A      20          ...
4    B      40          ...
5    B      20          ...
6    C      30          ...
7    C      20          ...
8    C      10          ...

ID is an identity field and the primary key and there are non-unique indexes on the Name and Timestamp fields.

ID是标识字段和主键,Name和Timestamp字段上有非唯一索引。

What is the most efficient way to get the most recent record for each item name, i.e. in the table above rows 1,4 and 6 should be returned as they are the most up-to-date entries for items A,B and C respectively.

获取每个项目名称的最新记录的最有效方法是什么,即在上面的表格中,应返回第1,4和6行,因为它们分别是项目A,B和C的最新条目。。

5 个解决方案

#1

SQL Server 2005 (onwards):

SQL Server 2005(从头开始):

WITH MostRecentRows AS
(
    SELECT ID, Name, Data,
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TimeStamp DESC) AS 'RowNumber'
    FROM MySchema.MyTable
) 
SELECT * FROM MostRecentRows 
WHERE RowNumber = 1

#2

Assuming there are no duplicate timestamps per name, something like this should work:

假设每个名称没有重复的时间戳,这样的事情应该有效:

SELECT ID, Name, Timestamp, Data
FROM test AS o
WHERE o.Timestamp = (SELECT MAX(Timestamp)
                     FROM test as i
                     WHERE i.name = o.name)

#3

SQL Server 2000:

SELECT
  ID, Name, Timestamp, Data
FROM
  DataTable
  INNER JOIN
  (
     SELECT ID, MAX(Timestamp) Timestamp FROM DataTable GROUP BY ID
  ) latest ON 
    DataTable.ID = Latest.ID AND 
    DataTable.Timestamp = Latest.Timestamp

#4

Another easy way :

另一个简单方法:

SELECT ID,Name,Timestamp, Data
FROM   Test_Most_Recent
WHERE Timestamp = (SELECT MAX(Timestamp)
                 FROM Test_Most_Recent
                 group by Name);

#5

If you are using SQL Server 2005/2008, then the CTE solution already listed by Mitch Weat is the best from a performance perspective. However, if you are using SQL Server 2000, then you can't assume there aren't duplicate Name | TimeStamp combinations. Use the following code to return only one record per name:

如果您使用的是SQL Server 2005/2008,那么从性能角度来看,Mitch Weat已经列出的CTE解决方案是最好的。但是,如果您使用的是SQL Server 2000,那么您不能假设没有重复的Name | TimeStamp组合。使用以下代码每个名称只返回一条记录:

SELECT ID
    , Name
    , TimeStamp
    , Data
FROM DataTable dt
INNER JOIN
    (SELECT Name
    , MIN(DataTable.ID) AS MinimumID
FROM DataTable  
INNER JOIN  
    (SELECT Name
        , MAX(Timestamp) AS Timestamp 
    FROM DataTable 
    GROUP BY Name) latest 
    ON DataTable.Name = Latest.Name
    AND DataTable.Timestamp = Latest.Timestamp
GROUP BY Name) MinimumLatest
ON dt.ID = MinimumLatest.ID

So if you add another record like 9 C 30, then this will only return ID 6. If you don't go this far, then you may end up return 9 C 30 and 6 C 30.

因此,如果您添加另一条记录,例如9 C 30,那么这只会返回ID 6.如果您没有这么做,那么您最终可能会返回9 C 30和6 C 30。

#1