Vinayak Prabha Vinayak Prabha - 5 months ago 26
SQL Question

SQL Server - SELECT one row with the MAX() value on a column

Here is my table table structure

id | Name | Date | VersionID
----+---------------+---------------+-------------------------------------
1 | Item 1 | 10/15/2012 | F8883CA4-3603-476D-BA33-1BBB6B87A79F
1 | Item 1 | 11/06/2012 | AA22DA27-87D1-42EC-96F6-A4846A45DF6B
1 | Item 1 | 11/06/2018 | AA22DA27-87D1-42EC-96F6-A4846A45DF6B
2 | Item 2 | 11/06/2015 | F8883CA4-3603-476D-BA33-1BBB6B87A79F
2 | Item 2 | 12/15/2012 | AA22DA27-87D1-42EC-96F6-A4846A45DF6B
2 | Item 2 | 1/19/2013 | F8883CA4-3603-476D-BA33-1BBB6B87A79F


From this table, for each version, I would like to get each item with max date.

Example

id | Name | Date | VersionID
----+---------------+---------------+-------------------------------------
1 | Item 1 | 10/15/2012 | F8883CA4-3603-476D-BA33-1BBB6B87A79F
1 | Item 1 | 11/06/2018 | AA22DA27-87D1-42EC-96F6-A4846A45DF6B
2 | Item 2 | 11/06/2015 | F8883CA4-3603-476D-BA33-1BBB6B87A79F
2 | Item 2 | 12/15/2012 | AA22DA27-87D1-42EC-96F6-A4846A45DF6B


I tried
rank
and
dense_rank
functions but the logic I tried isn't returning the expected results. Thoughts ?

Answer

The normal way is to use row_number:

select id, name, date, versionid
from (select s.*,
             row_number() over (partition by id, version_id order by date desc) as seqnum
      from structure s
     ) s
where seqnum = 1;