SQL inner join on a column based on max value from another column

Question

I have two tables, one "master" is a master list of names and the second "scenario" is a list of multiple scenarios for each name from the master list. I want my INNER JOIN query to fetch the master list of ID with the column status from "scenario" table but only the most recent status based on scenarioID. Here's the code that I've tried and tables with desired output

SELECT DISTINCT a.[user], a.ID, a.Name, b.status
from master a
INNER JOIN scenario b ON a.ID = b.ID
WHERE
    b.scenarioID = (
           SELECT max(scenarioID) FROM scenario c2 WHERE c2.ID=c.ID)

Master

ID    user    Name
425   John    Skyline
426   John    Violin
427   Joe     Pura

Scenario

ID    ScenarioID    status
425   1             active
425   2             active
425   3             done
426   1             active
426   2             active
427   1             done

Desired output

ID    user    Name    status
425   John    Skyline done
426   John    Violin  active
427   Joe     Pura    done

http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group is for mysql not sql server so there are different ways to solve the problem. Also this question involves joining to the last record whereas the duplicate question is just trying to get the last within a table. — Daniel Gimenez, May 26 '16 at 20:27

score 6 · Answer 1 · answered May 26 '16 at 20:16

You can do this with a CROSS APPLY looking up the most recent for each value:

Select  M.ID, M.[User], M.Name, X.Status
From    [Master]    M
Cross Apply
(
    Select  Top 1 S.Status
    From    Scenario    S
    Where   S.ID = M.ID
    Order By S.ScenarioID Desc
) X

Another way you could do it is with a ROW_NUMBER() PARTITIONED on the ID and ORDERED by the ScenarioID DESC:

;With OrderedStatuses As
(
    Select  M.Id, M.[User], M.Name, S.Status,
            Row_Number() Over (Partition By S.Id Order By S.ScenarioID Desc) RN
    From    [Master]    M
    Join    Scenario    S   On  S.Id = M.Id
)
Select  Id, [User], Name, Status
From    OrderedStatuses
Where   RN = 1

Edmond Quinton · Answer 2 · 2016-05-26T20:31:43.910

1

If you have SQL Server 2008 or later you can use the ROW_NUMBER() function to achieve what you want. It will avoid querying the same table twice or performing joins.

SELECT *
FROM    (

            SELECT   a.[user]
                    ,a.ID
                    ,a.Name
                    ,b.status 
                    ,ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY b.scenarioID DESC) AS VersionRank
            from [master] a INNER JOIN scenario b ON a.ID = b.ID 
        ) Result
WHERE   Result.VersionRank = 1

edited May 26 '16 at 20:31

answered May 26 '16 at 20:18

Edmond Quinton

1,709
9
10

What's the purpose of the `DISTINCT` in this query? – Joe Farrell May 26 '16 at 20:25
@JoeFarrell good question I suspect nothing, I simply copied the original query from the question and modified to suit. – Edmond Quinton May 26 '16 at 20:30
1

@JoeFarrell Removed unnecessary distinct :) – Edmond Quinton May 26 '16 at 20:32

score 1 · Accepted Answer · answered May 26 '16 at 20:21

Here's a slightly different formulation that uses a CTE, which I generally find easier to read than a subquery (though of course, your mileage may vary).

declare @Master table
(
    ID bigint,
    [user] varchar(16),
    Name varchar(16)
);

declare @Scenario table
(
    ID bigint,
    ScenarioID bigint,
    [status] varchar(16)
);

insert @Master values
    (425, 'John', 'Skyline'),
    (426, 'John', 'Violin'),
    (427, 'Joe', 'Pura');
insert @Scenario values
    (425, 1, 'active'),
    (425, 2, 'active'),
    (425, 3, 'done'),
    (426, 1, 'active'),
    (426, 2, 'active'),
    (427, 1, 'done');

with ReversedScenarioCTE as
(
    select
        ID,
        [status],
        rowNumber = row_number() over (partition by ID order by ScenarioID desc)
    from
        @Scenario
)
select
    M.ID,
    M.[user],
    M.Name,
    S.[status]
from
    @Master M
    inner join ReversedScenarioCTE S on
        M.ID = S.ID and
        S.rowNumber = 1;

SQL inner join on a column based on max value from another column

3 Answers3