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
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
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;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With