per - sql top group by




獲取每條記錄的最新日期 (3)

這是我該怎麼做

 ;WITH CTE AS 
 (
 Select PartId, MAX(LASTUPDATED) as 'MAXX' from part group by PartId
 ) 
 Select TransActionId, p.PartId, Description, p.LASTUPDATED
 from Part p
 inner join CTE on p.LastUpdated = CTE.MAXX
                and p.PartId = CTE.PartId           

我有一個歷史記錄表(如日誌)記錄對部分的更改:

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的含義,但這不是我現在面臨的問題。 我需要編寫一個查詢來返回每個部分的最新維護。 所以在這種情況下,我的預期產出將是:

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

說明:例如,2009-01-02 20:00:04.700等完成了對零件ID#1的最新維護。

我已經嘗試了SELECT DISTINCT但它不會工作,因為基本上每一行都會有所不同。 我完全沒有線索 如果我使用MAX(Last Updated) ,它將只返回整個表的一行。

編輯:在任何情況下,我不被允許使用動態查詢。


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

;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

select 
    TransactionID, PartID, Description, LastUpdated
from 
    History H
where
    LastUpdated = 
    (
        select 
            max(LastUpdated) 
        from 
            History
        where
            PartID = H.PartID
    )




greatest-n-per-group