locked
Case and Rank at the Same Time RRS feed

  • Question

  • Hello everyone,

    I'm trying to use rank while also using case. Is that possible?

    StoreID         Level                     ID                     Location        

    1                    Manager                5353                  Null         

    1                    Employee               4343                 Electronics 1

    65                  Employee               5675                 Electronics 2

    I'm trying to make a column that will rank users. Manager will ALWAYS be the first one. Employees would be ranked by their ID. Whoever has the lower ID would come first and so on.

    I tried to use this query, but it doesn't work correctly. One of the employee's IDs comes before the manager, so it's ranking both the employee and manager as 1.

    SELECT 
    DENSE_RANK() OVER (Partition by b.Store_ID, 
    Case when Level = 'Manager' 
    Then 0 
    End Order by ID) as 'EmpRank'
    FROM Users

    Thank you.



    • Edited by Lanmanna Friday, November 2, 2018 6:05 PM
    Friday, November 2, 2018 3:37 PM

All replies

  • DECLARE @Users TABLE (
    	[Level] varchar(50),
    	[ID] int,
    	[Location] varchar(50) 
    );
    
    INSERT INTO @Users VALUES 
    ('Manager', 5353, NULL), ('Employee', 4343, 'Electronics 1'), ('Employee', 3430, 'Electronics 2');
    
    WITH CTE_Manager AS (
    	SELECT [Level], [ID], [Location], 1 AS Level_Ranked, ROW_NUMBER() OVER (ORDER BY [ID]) AS Ranked
    	FROM @Users
    	WHERE [Level] = 'Manager'
    ),
    CTE_Employee AS (
    	SELECT [Level], [ID], [Location], 2 AS Level_Ranked, ROW_NUMBER() OVER (ORDER BY [ID]) AS Ranked
    	FROM @Users
    	WHERE [Level] = 'Employee'
    )
    
    SELECT [Level], [ID], [Location], [Level_Ranked], [Ranked]
    FROM CTE_Manager
    UNION ALL 
    SELECT [Level], [ID], [Location], [Level_Ranked], [Ranked]
    FROM CTE_Employee
    ORDER BY Level_Ranked, Ranked;


    A Fan of SSIS, SSRS and SSAS

    Friday, November 2, 2018 4:05 PM
  • Use RANK function:

    DECLARE @Users TABLE (
    	[Level] varchar(50),
    	[ID] int,
    	[Location] varchar(50) 
    );
    
    INSERT INTO @Users VALUES 
    ('Manager', 5353, NULL), ('Employee', 4343, 'Electronics 1'), ('Employee', 3430, 'Electronics 2');
    
    SELECT [Level], [ID], [Location], RANK() OVER (PARTITION BY (CASE WHEN [Level] = 'Manager' THEN 1 ELSE 2 END) ORDER BY [ID] ASC) AS Ranked
    FROM @Users;


    A Fan of SSIS, SSRS and SSAS

    Friday, November 2, 2018 4:15 PM