CODEWITHSUNDEEP
sqlsql-server
VPP
VPP

Reputation: 779

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 ?

Upvotes: 0

Views: 65

Answers (2)

mohan111
mohan111

Reputation: 8865

We can get result by using MAX Condition and Co- related Subquery also

DECLARE @Table1 TABLE 
    (id int, Name varchar(6), Date datetime, VersionID varchar(36))
;

INSERT INTO @Table1
    (id, Name, Date, VersionID)
VALUES
    (1, 'Item 1', '2012-10-15 05:30:00', 'F8883CA4-3603-476D-BA33-1BBB6B87A79F'),
    (1, 'Item 1', '2012-11-06 05:30:00', 'AA22DA27-87D1-42EC-96F6-A4846A45DF6B'),
    (1, 'Item 1', '2018-11-06 05:30:00', 'AA22DA27-87D1-42EC-96F6-A4846A45DF6B'),
    (2, 'Item 2', '2015-11-06 05:30:00', 'F8883CA4-3603-476D-BA33-1BBB6B87A79F'),
    (2, 'Item 2', '2012-12-15 05:30:00', 'AA22DA27-87D1-42EC-96F6-A4846A45DF6B'),
    (2, 'Item 2', '2013-01-19 05:30:00', 'F8883CA4-3603-476D-BA33-1BBB6B87A79F')
;

Select T.id,T.Name,TT.Dt,T.VersionID from @Table1 T
INNER JOIN (
select id,Name,MAX(Date)Dt,VersionID from @Table1 
GROUP BY id,Name,VersionID)TT
ON T.id = TT.id AND T.Date = TT.Dt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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;

Upvotes: 3

Related Questions