question

CarltonPatterson-7576 avatar image
0 Votes"
CarltonPatterson-7576 asked ·

Unable to get ranking function to work as expected


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)
sql-server-transact-sql
ranking.png (9.2 KiB)
ranking2.png (5.1 KiB)
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@CarltonPatterson-7576,

Please explain some logic to get your desired output.

0 Votes 0 ·

Do you have any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

Hi @CarltonPatterson-7576,

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.





·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PaulStaniforth-8963 avatar image
0 Votes"
PaulStaniforth-8963 answered ·

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

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;
· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@GuoxiongYuan-7218,

Unfortunately, we don't know what is the desired output for the provided DDL and sample data population. Same with the logic.

0 Votes 0 ·