March 18, 2021 at 12:19 pm
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)
March 18, 2021 at 1:31 pm
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.
March 18, 2021 at 1:33 pm
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/61537March 18, 2021 at 1:52 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply