0

I have following student table

ID     Version      Name       Email
1        1         Johan        [email protected]
1        2         Johan 1      [email protected]
2        1         Saif         [email protected]
2        2         Saif  1      [email protected]
2        3         Saif  4      [email protected]

I want to select student records based top version.

ID     Version      Name       Email
1       2          Johan 1      [email protected]
2       3          Saif  4      [email protected]

1 Answer 1

2

Key points here, are :

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

and

WHERE RN = 1 

Here is:

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

INSERT INTO Student
    ([ID], [Version], [Name], [Email])
VALUES
    (1, 1, 'Johan', '[email protected]'),
    (1, 2, 'Johan1', '[email protected]'),
    (2, 1, 'Saif', '[email protected]'),
    (2, 2, 'Saif1', '[email protected]'),
    (2, 3, 'Saif4', '[email protected]')
;

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

output:

ID  Version Name    Email
1   2   Johan1  [email protected]
2   3   Saif4   [email protected]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.