Home Select specific value for all corresponding rows

# Select specific value for all corresponding rows

sǝɯɐſ
1#
sǝɯɐſ Published in 2018-02-13 17:37:33Z
 I am trying to get a result set of Top Customers which is ordered by a rank value based on year and total. Easy, right? BUT, now I want to specify a year, and have ALL rows for that customer return the rank value for that year. For example, say I have the following data (rank hardcoded for ease): SELECT * FROM ( SELECT 'Customer A' as Cust,'123.45' as Total,'2016' as [year],1 as [rank] UNION SELECT 'Customer A','123.45','2017',3 UNION SELECT 'Customer B','46.67','2016',2 UNION SELECT 'Customer B','423.45','2017',1 UNION SELECT 'Customer B','123.45','2018',1 UNION SELECT 'Customer C','23.45','2016',3 UNION SELECT 'Customer C','223.45','2017',2 UNION SELECT 'Customer C','23.45','2018',2 ) as a ORDER BY a.[year], a.[rank]  If I specify year 2016, I want to pick the rank value for the year 2016 for each customer, and return that for the customer for each of that customer's rows in the result set - should look like the following: => The closest I can manage is the following, but it just NULLs the other cells: DECLARE @RankBy VARCHAR(4) = '2016' SELECT [Year], Cust, [Total], [rank] = (SELECT a.[rank] WHERE Cust = a.Cust AND [Year] = @RankBy) FROM ( SELECT 'Customer A' as Cust,'123.45' as Total,'2016' as [year],1 as [rank] UNION SELECT 'Customer A','123.45','2017',3 UNION SELECT 'Customer B','46.67','2016',2 UNION SELECT 'Customer B','423.45','2017',1 UNION SELECT 'Customer B','123.45','2018',1 UNION SELECT 'Customer C','23.45','2016',3 UNION SELECT 'Customer C','223.45','2017',2 UNION SELECT 'Customer C','23.45','2018',2 ) as a ORDER BY a.Cust, a.rank  I know I can do this with a temp table and an update statement, but I'm trying to find a way to do it in a single select statement if possible. (In case anyone is wondering, this is for an SSRS Report, but I don't see that being relevant here.)
dnoeth
2#
 Try a Windowed Aggregate over a CASE: MIN(CASE WHEN [Year] = @RankBy THEN a.[rank] END) OVER (PARTITION BY Cust)