Dense_Rank function issue. Unable to Rank Field in Descending order

Question

Post reply

Dense_Rank function issue. Unable to Rank Field in Descending order

carlton 84646

Ten Centuries

Points: 1026

March 18, 2021 at 12:19 pm

#3859385

Hello Community,

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

ranking

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:

ranking2

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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246706 More actions · Answer 1

Something like this?

WITH ranked
AS (SELECT te.department
          ,te.salary
          ,rnk = DENSE_RANK() OVER (PARTITION BY te.department ORDER BY te.salary DESC)
    FROM dbo.twitter_employee te)
SELECT ranked.department
      ,ranked.salary
FROM ranked
WHERE ranked.rnk <= 3
GROUP BY ranked.department
        ,ranked.salary
ORDER BY ranked.department
        ,ranked.salary DESC;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

I think it's just a matter of adding this to the end of your query

ORDER BY SubQuery.department,SubQuery.myrank

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Phil Parkin SSC Guru Points: 246706 More actions · Answer 3

Mark Cowne wrote:

I think it's just a matter of adding this to the end of your query
ORDER BY SubQuery.department,SubQuery.myrank

I had assumed that the DENSE_RANK() was there for a reason! If your suggestion is right, the query can be simplified:

SELECT DISTINCT
       te.department
      ,te.salary
FROM dbo.twitter_employee te
ORDER BY te.department
        ,te.salary DESC;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.