w3resource

SQL Challenges-1: Find highest three unique salaries of each department

SQL Challenges-1: Exercise-72 with Solution

From the following table write a query in SQL to find the highest three unique salaries for each department. Return department ID and three highest unique salaries. Arranged the result set in ascending order on department ID and descending order on salaries.

Table: employees

Structure:

FieldTypeNullKeyDefaultExtra
employee_idintNOPRI
emp_namevarchar(25)YES
hire_datedate YES
job_idvarchar(25)YES
salarydecimal(10,2)YES
manager_idintYES
department_idintYES

Data:

employee_idemp_namehire_datejob_idsalarymanager_iddepartment_id
100Steven1987-06-17AD_PRES24000.00090
101Neena1987-06-18AD_VP17000.0010090
102Lex1987-06-19AD_VP17000.0010090
103Alexander1987-06-20IT_PROG9000.00102 60
104Bruce1987-06-21IT_PROG6000.00103 60
105David1987-06-22IT_PROG4800.00103 60
106Valli1987-06-23IT_PROG4800.00103 60
107Diana1987-06-24IT_PROG4200.00103 60
108Nancy1987-06-25FI_MGR12000.00101 100
109Daniel1987-06-26FI_ACCOUNT9000.00108 100
110John1987-06-27FI_ACCOUNT8200.00108 100
111Ismael1987-06-28FI_ACCOUNT7700.00108 100
112Jose Manuel1987-06-29FI_ACCOUNT7800.00108 100
113Luis1987-06-30FI_ACCOUNT6900.00108 100
114Den1987-07-01PU_MAN11000.00 100 30
115Alexander1987-07-02PU_CLERK3100.00114 30
116Shelli1987-07-03PU_CLERK2900.00114 30
117Sigal1987-07-04PU_CLERK2800.00 114 30
133Jason1987-07-20ST_CLERK3300.00122 50
134Michael1987-07-21ST_CLERK2900.00122 50
135Ki1987-07-22ST_CLERK2400.00122 50
136Hazel1987-07-23ST_CLERK2200.00 122 50
137Renske1987-07-24ST_CLERK3600.00123 50
138Stephen1987-07-25ST_CLERK3200.00123 50
139John1987-07-26ST_CLERK2700.0012350

Sample Solution:

SQL Code(MySQL):

create table employees (
employee_id integer(4) not null unique,
emp_name varchar(25),
hire_date date,
job_id varchar(25),
salary decimal(10,2),
manager_id integer(4),
department_id integer(4)
);


insert into employees values( 100,'Steven     ','1987-06-17','AD_PRES   ',24000.00,         0,   90);
insert into employees values( 101,'Neena      ','1987-06-18','AD_VP     ',17000.00,       100,   90);
insert into employees values( 102,'Lex        ','1987-06-19','AD_VP     ',17000.00,       100,   90);
insert into employees values( 103,'Alexander  ','1987-06-20','IT_PROG   ', 9000.00,       102,   60);
insert into employees values( 104,'Bruce      ','1987-06-21','IT_PROG   ', 6000.00,       103,   60);
insert into employees values( 105,'David      ','1987-06-22','IT_PROG   ', 4800.00,       103,   60);
insert into employees values( 106,'Valli      ','1987-06-23','IT_PROG   ', 4800.00,       103,   60);
insert into employees values( 107,'Diana      ','1987-06-24','IT_PROG   ', 4200.00,       103,   60);
insert into employees values( 114,'Den        ','1987-07-01','PU_MAN    ',11000.00,       100,   30);
insert into employees values( 115,'Alexander  ','1987-07-02','PU_CLERK  ', 3100.00,       114,   30);
insert into employees values( 116,'Shelli     ','1987-07-03','PU_CLERK  ', 2900.00,       114,   30);
insert into employees values( 117,'Sigal      ','1987-07-04','PU_CLERK  ', 2800.00,       114,   30);
insert into employees values( 108,'Nancy      ','1987-06-25','FI_MGR    ',12000.00,       101,  100);
insert into employees values( 109,'Daniel     ','1987-06-26','FI_ACCOUNT', 9000.00,       108,  100);
insert into employees values( 110,'John       ','1987-06-27','FI_ACCOUNT', 8200.00,       108,  100);
insert into employees values( 111,'Ismael     ','1987-06-28','FI_ACCOUNT', 7700.00,       108,  100);
insert into employees values( 112,'Jose Manuel','1987-06-29','FI_ACCOUNT', 7800.00,       108,  100);
insert into employees values( 113,'Luis       ','1987-06-30','FI_ACCOUNT', 6900.00,       108,  100);
insert into employees values( 133,'Jason      ','1987-07-20','ST_CLERK  ', 3300.00,       122,   50);
insert into employees values( 134,'Michael    ','1987-07-21','ST_CLERK  ', 2900.00,       122,   50);
insert into employees values( 135,'Ki         ','1987-07-22','ST_CLERK  ', 2400.00,       122,   50);
insert into employees values( 136,'Hazel      ','1987-07-23','ST_CLERK  ', 2200.00,       122,   50);
insert into employees values( 137,'Renske     ','1987-07-24','ST_CLERK  ', 3600.00,       123,   50);
insert into employees values( 138,'Stephen    ','1987-07-25','ST_CLERK  ', 3200.00,       123,   50);
insert into employees values( 139,'John       ','1987-07-26','ST_CLERK  ', 2700.00,       123,   50);


SELECT DISTINCT department_id, salary 
FROM (SELECT department_id,salary,dense_rank() 
    OVER(PARTITION BY department_id ORDER BY salary DESC) as top3
    from employees) t
WHERE top3<=3
ORDER BY department_id ASC,salary DESC

Sample Output:

department_id|salary  |
-------------+--------+
           30|11000.00|
           30| 3100.00|
           30| 2900.00|
           50| 3600.00|
           50| 3300.00|
           50| 3200.00|
           60| 9000.00|
           60| 6000.00|
           60| 4800.00|
           90|24000.00|
           90|17000.00|
          100|12000.00|
          100| 9000.00|
          100| 8200.00|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find salespersons who not yet made any sale transaction.
Next: Find salespersons who not yet made any sale transaction.



Follow us on Facebook and Twitter for latest update.