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

Last update on June 04 2024 13:22:29 (UTC/GMT +8 hours)

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:

Field	Type	Null	Key
employee_id	int	NO	PRI
emp_name	varchar(25)	YES
hire_date	date	YES
job_id	varchar(25)	YES
salary	decimal(10,2)	YES
manager_id	int	YES
department_id	int	YES

Data:

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

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.