0

I have following student table

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

I want to select student records based top version.

ID     Version      Name       Email
1       2          Johan 1      Johan@email.com
2       3          Saif  4      saif1@email.com
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', '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 

output:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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