another gem
2014-06-11 08:13:08
I would like to simplify these 3x Select statements into 1 (but not by using sub-selects). The 2nd and 3rd ones seem to be just shaping the data from #1 in a long-winded manor.
2014-06-11 09:55:57
done! (although I couldn't quite do it in one select) using a crazy case within the row_number function …
select GlobalPlanObjectID, MissingGlobalObjectLifeTimeID, MissingLifeTimeStatus, GlobalObjectStatusID from (SELECT gpo.GlobalPlanObjectID GlobalPlanObjectID, gol.GlobalObjectLifeTimeID MissingGlobalObjectLifeTimeID, sta.[Status] MissingLifeTimeStatus, GlobalObjectStatusID, ROW_NUMBER() OVER (PARTITION BY GlobalPlanObjectID order by (CASE gol.GlobalObjectStatusID WHEN 20 THEN 1 WHEN 25 THEN 2 WHEN 24 THEN 3 ELSE 4 END)) AS RowNumber FROM dbo.tblGlobalPlanObject gpo JOIN dbo.tblGlobalObjectLifeTime gol ON gpo.GlobalPlanObjectID = gol.GlobalObjectID JOIN dbo.tblStatus sta ON gol.GlobalObjectStatusID = sta.StatusID) MissingLifeTimes where rownumber = 1