Unable to get ranking function to work as expected

Question

Hello Community,

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

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:

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)  

Answer 1

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.

Answer 2

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

Answer 3

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;