Unable to get ranking function to work as expected

Carlton Patterson 21 Reputation points
2021-03-18T12:28:44.113+00:00

Hello Community,

I'm trying to obtain the following output from a dataset:

79208-ranking.png

I have compiled the following SQL query to achieve this:

SELECT  
  SubQuery.department  
 ,SubQuery.salary  
FROM (SELECT  
    twitter_employee.department  
   ,twitter_employee.salary  
   ,DENSE_RANK() OVER (PARTITION BY twitter_employee.department ORDER BY twitter_employee.salary DESC) AS myrank  
  FROM dbo.twitter_employee  
  GROUP BY twitter_employee.department  
          ,twitter_employee.salary) SubQuery  

However, I keep on getting the following output:

79209-ranking2.png

Can someone tweak my code such that I get the output in the first image?

I have included sample data:

CREATE TABLE twitter_employee (  
    id int,  
    first_name varchar(50),  
    last_name varchar(50),  
    age int,  
    sex varchar(50),  
    employee_title varchar(50),  
    department varchar(50),  
    salary int,  
    target int,  
    bonus int,  
    email varchar(50),  
    city varchar(50),  
    address varchar(50),  
    manager_id int)  
  
INSERT twitter_employee VALUES  
(1,'Allen','Wang',55,'F','Manager','Management',200000,0,300,'Allen@company.com','California','23St',1),  
(13,'Katty','Bond',56,'F','Manager','Management',150000,0,300,'Katty@company.com','Arizona','',1),  
(19,'George','Joe',50,'M','Manager','Management',100000,0,300,'George@company.com','Florida','26St',1),  
(11,'Richerd','Gear',57,'M','Manager','Management',250000,0,300,'Richerd@company.com','Alabama','',1),  
(10,'Jennifer','Dion',34,'F','Sales','Sales',100000,200,150,'Jennifer@company.com','Alabama','',13),  
(18,'Laila','Mark',26,'F','Sales','Sales',100000,200,150,'Laila@company.com','Florida','23St',11),  
(20,'Sarrah','Bicky',31,'F','Senior Sales','Sales',200000,200,150,'Sarrah@company.com','Florida','53St',19),  
(21,'Suzan','Lee',34,'F','Sales','Sales',130000,200,150,'Suzan@company.com','Florida','56St',19),  
(22,'Mandy','John',31,'F','Sales','Sales',130000,200,150,'Mandy@company.com','Florida','45St',19),  
(23,'Britney','Berry',45,'F','Sales','Sales',120000,200,100,'Britney@company.com','Florida','86St',19),  
(24,'Adam','Morris',30,'M','Sales','Sales',130000,200,100,'Adam@company.com','Alabama','24St',19),  
(25,'Jack','Mick',29,'M','Sales','Sales',130000,200,100,'Jack@company.com','Hawaii','54St',19),  
(26,'Ben','Ten',43,'M','Sales','Sales',130000,150,100,'Ben@company.com','Hawaii','23St',19),  
(27,'Tom','Fridy',32,'M','Sales','Sales',120000,200,150,'Tom@company.com','Hawaii','23St',1),  
(28,'Morgan','Matt',25,'M','Sales','Sales',120000,200,150,'Morgan@company.com','Hawaii','28St',1),  
(29,'Antoney','Adam',34,'M','Sales','Sales',130000,180,150,'Antoney@company.com','Hawaii','45St',1),  
(30,'Mark','Jon',28,'M','Sales','Sales',120000,200,150,'Mark@company.com','Alabama','43St',1),  
(2,'Joe','Jack',32,'M','Sales','Sales',100000,200,150,'Joe@company.com','California','22St',1),  
(3,'Henry','Ted',31,'M','Senior Sales','Sales',200000,200,150,'Henry@company.com','California','42St',1),  
(4,'Sam','Mark',25,'M','Sales','Sales',100000,120,150,'Sam@company.com','California','23St',1),  
(5,'Max','George',26,'M','Sales','Sales',130000,200,150,'Max@company.com','California','24St',1),  
(8,'John','Ford',26,'M','Senior Sales','Sales',150000,140,100,'Molly@company.com','Alabama','45St',13),  
(9,'Monika','William',33,'F','Sales','Sales',100000,200,100,'Molly@company.com','Alabama','',13),  
(17,'Mick','Berry',44,'M','Senior Sales','Sales',220000,200,150,'Mick@company.com','Florida','',11),  
(12,'Shandler','Bing',23,'M','Auditor','Audit',110000,200,150,'Shandler@company.com','Arizona','',11),  
(14,'Jason','Tom',23,'M','Auditor','Audit',100000,200,150,'Jason@company.com','Arizona','',11),  
(16,'Celine','Anston',27,'F','Auditor','Audit',100000,200,150,'Celine@company.com','Colorado','',11),  
(15,'Michale','Jackson',44,'F','Auditor','Audit',70000,150,150,'Michale@company.com','Colorado','',11),  
(6,'Molly','Sam',28,'F','Sales','Sales',140000,100,150,'Molly@company.com','Arizona','24St',13),  
(7,'Nicky','Bat',33,'F','Sales','Sales',140000,400,100,'Molly@company.com','Arizona','35St',13)  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-03-19T07:31:38.4+00:00

    Hi @Carlton Patterson ,

    Welcome to microsoft TSQL Q&A forum!

    Your code is correct.It did get the expected output result you provided:

         SELECT  
           SubQuery.department  
          ,SubQuery.salary  
          ,myrank  
         FROM (SELECT  
             twitter_employee.department  
            ,twitter_employee.salary  
            ,DENSE_RANK() OVER (PARTITION BY twitter_employee.department ORDER BY twitter_employee.salary DESC) AS myrank  
           FROM dbo.twitter_employee  
           GROUP BY twitter_employee.department  
                   ,twitter_employee.salary) SubQuery  
    

    Output:

        department salary myrank  
        Audit 110000 1  
        Audit 100000 2  
        Audit 70000 3  
        Management 250000 1  
        Management 200000 2  
        Management 150000 3  
        Management 100000 4  
        Sales 220000 1  
        Sales 200000 2  
        Sales 150000 3  
        Sales 140000 4  
        Sales 130000 5  
        Sales 120000 6  
        Sales 100000 7  
    

    However, it is worth noting that in DENSE_RANK() OVER (PARTITION BY twitter_employee.department ORDER BY twitter_employee.salary DESC), order by is only used to indicate the order in which to rank, and cannot determine the order of the final result.

    If you don't want to add the myrank column to the select list, you need to add an order by clause at the end of the query according to Paul Staniforth-8963.

    In addition, you can use cte instead of subqueries, which seems to be easier to read:

    ;WITH cte   
    as(SELECT department,salary,  
              DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS myrank  
       FROM dbo.twitter_employee  
       GROUP BY department,salary)  
       
     SELECT department,salary  
     FROM cte  
     ORDER BY department,salary Desc  
    

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Paul Staniforth 76 Reputation points
    2021-03-18T13:01:05.603+00:00

    Hi Carlton,

    You can achieve the desired output by adding an order by to the overall query: -

      SELECT
       SubQuery.department
      ,SubQuery.salary
     FROM (SELECT
         twitter_employee.department
        ,twitter_employee.salary
        ,DENSE_RANK() OVER (PARTITION BY twitter_employee.department ORDER BY twitter_employee.salary DESC) AS myrank
       FROM dbo.twitter_employee
       GROUP BY twitter_employee.department
               ,twitter_employee.salary) SubQuery
        order by SubQuery.department, SubQuery.salary desc
    

    This will give you the output of: -

    department salary
    Audit 110000
    Audit 100000
    Audit 70000
    Management 250000
    Management 200000
    Management 150000
    Management 100000
    Sales 220000
    Sales 200000
    Sales 150000
    Sales 140000
    Sales 130000
    Sales 120000
    Sales 100000

    0 comments No comments

  2. Guoxiong 8,201 Reputation points
    2021-03-18T14:35:01.387+00:00

    You can simply use DISTINCT Department and Salary to get your expected results:

    SELECT DISTINCT [department], [salary]
    FROM [dbo].[twitter_employee]
    ORDER BY [department] ASC, [salary] DESC;