Geek Answers Handbook

Internal SQL join in a column based on the maximum value from another column

I have two tables, one “master” is the main list of names, and the second “script” is a list of several scripts for each name from the main list. I want my INNER JOIN query to retrieve the main list of identifiers with column status from the script table, but only the most recent status based on the script identifier. Here is the code I tried and the tables with the 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 Conclusion

 ID user Name status 425 John Skyline done 426 John Violin active 427 Joe Pura done
+5
source share
3 answers

Here's a slightly different wording that uses CTE, which I usually find easier to read than a subquery (although, 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;
+1
source

You can do this with CROSS APPLY to find the last value 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 this is ROW_NUMBER() PARTITIONED on ID and ORDERED using 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
+3
source

If you have SQL Server 2008 or later, you can use the ROW_NUMBER () function to achieve what you want. This avoids 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
+1
source

More articles:


All Articles