pythonnewb pythonnewb - 6 months ago 15
SQL Question

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

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

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;