根據複合鍵從列表中選擇

我有以下學生表

ID     Version      Name       Email
1        1         Johan        Johan@email.com
1        2         Johan 1      Johan@email.com
2        1         Saif         saif@email.com
2        2         Saif  1      saif2@email.com
2        3         Saif  4      saif1@email.com

我想選擇基於學生記錄的頂級版本。

ID     Version      Name       Email
1       2          Johan 1      Johan@email.com
2       3          Saif  4      saif1@email.com

這裡的要點是：

ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Version] DESC) AS RN

和

WHERE RN = 1 

這是：

CREATE TABLE Student
   ([ID] int, [Version] int, [Name] varchar(6), [Email] varchar(15))
;

INSERT INTO Student
   ([ID], [Version], [Name], [Email])
VALUES
   (1, 1, 'Johan', 'Johan@email.com'),
   (1, 2, 'Johan1', 'Johan@email.com'),
   (2, 1, 'Saif', 'saif@email.com'),
   (2, 2, 'Saif1', 'saif2@email.com'),
   (2, 3, 'Saif4', 'saif1@email.com')
;

-- select


;WITH StudentCTE AS
(
SELECT [ID], [Version], [Name], [Email]
 ,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Version] DESC) AS RN
FROM Student
)

SELECT  [ID], [Version], [Name], [Email]
FROM StudentCTE 
WHERE RN = 1 

輸出：

ID  Version Name    Email
1   2   Johan1  Johan@email.com
2   3   Saif4   saif1@email.com

引用自：https://dba.stackexchange.com/questions/202812