获取每条记录的最新日期 [英] Get the latest date for each record

问题描述

我有一个历史记录表(如日志)，它记录了零件的更改:

I have a History table (like a log) that records changes to parts:

TransactionID    Part ID          Description        Last Updated
1                1                Fixed O-ring       2006-03-14 20:00:04.700
2                2                Replaced coil      2009-01-02 20:00:04.700
3                1                Replaced coil      2009-01-02 20:00:04.700
4                1                Replaced LED       2002-08-20 20:00:04.700      
5                2                Sealed leakage     2007-03-08 20:00:04.700   
6                3                Replace connector  2004-05-16 20:00:04.700

我还有另一个表，该表将显示每个部件ID的含义，但这不是我现在面临的问题.我需要编写一个查询，以返回对每个零件所做的最新维护的查询.因此，在这种情况下，我的预期输出将是:

I have another table that will show what each Part ID stands for, but that is not the problem I'm facing now. I'm required to write a query that returns the latest maintenance done on every parts. So in this case, my expected output would be:

TransactionID    Part ID          Description        Last Updated
2                2                Replaced coil      2009-01-02 20:00:04.700    
3                1                Replaced coil      2009-01-02 20:00:04.700 
6                3                Replace connector  2004-05-16 20:00:04.700

说明:例如，部件ID#1的最新维护已于2009-01-02 20:00:04.700等完成.

Explanation: For example, the latest maintenance for Part ID #1 was completed on 2009-01-02 20:00:04.700 and so on.

我已经尝试过SELECT DISTINCT，但是它不会起作用，因为基本上每一行都是不同的.我完全不知所措.如果使用MAX(Last Updated)，它将仅返回整个表的一行.

I have tried SELECT DISTINCT but it won't work because basically every rows will be different. I'm completely out of clue. And if I use MAX(Last Updated), it will only return one row of the entire table.

在任何情况下，我都不允许使用动态查询.

Edited: In any case, I am NOT allowed to use Dynamic query.

推荐答案

SELECT TransactionID
      ,PartID
      ,[Description]
      ,[Last Updated]
FROM (
    SELECT TransactionID
          ,PartID
          ,[Description]
          ,[Last Updated]
          ,ROW_NUMBER() OVER (PARTITION BY [PartID] ORDER BY [Last Updated] DESC) RN 
    FROM TableName
    )A
WHERE A.RN = 1

或者您可以使用CTE

Or you can use CTE

;WITH CTE AS
  (
    SELECT TransactionID
          ,PartID
          ,[Description]
          ,[Last Updated]
          ,ROW_NUMBER() OVER (PARTITION BY [PartID] ORDER BY [Last Updated] DESC) RN 
    FROM TableName
  )
SELECT TransactionID
      ,PartID
      ,[Description]
      ,[Last Updated]  
FROM CTE 
WHERE A.RN = 1

这篇关于获取每条记录的最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！