SQL inner join on a column based on max value from another column [duplicate]

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

Answer 1

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

Answer 2

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;

Answer 3

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