Category: mysql

In this post, let us learn to get the third highest salary for each department in a table using MySQL.

MySQL table creation

In order to find the third highest salary for each department in a table using MySQL, we will create a table like below.

The following table contains the salary details of a few employees across different departments.

Create Table Query

create table employee (Dep_name varchar(20),Emp_id int,Salary int)

Insert Query

insert into Employee values('Computer',564,1400);
insert into Employee values('Computer',123,2500);
insert into Employee values('Computer',943,3200);
insert into Employee values('History',987,3450);
insert into Employee values('Economy',456,4500);
insert into Employee values('Economy',678,6700);
insert into Employee values('Economy',789,7200);

Table Data

mysql> select * from Employee;
+----------+--------+--------+
| Dep_name | Emp_id | Salary |
+----------+--------+--------+
| Computer |    564 |   1400 |
| Computer |    123 |   2500 |
| Computer |    943 |   3200 |
| History  |    987 |   3450 |
| Economy  |    456 |   4500 |
| Economy  |    678 |   6700 |
| Economy  |    789 |   7200 |
| Computer |    324 |   2500 |
+----------+--------+--------+
8 rows in set (0.00 sec)

nth Salary calculation for each group

We shall find the nth highest salary with the help of dense rank funtion available in MySQL.Here comes the syntax and usage of it.

Syntax :

DENSE_RANK() OVER (PARTITION BY <columnname> ORDER BY <columnname> desc)

The dense_rank helps us in ranking the records over each partition. To get to know about rank and dense rank well, Please refer to the below link.

https://beginnersbug.com/rank-and-dense-rank-in-pyspark-dataframe/

The partition by clause divides the entire data into groups based on the column specified. 

The order by clause sorts the column within each group whose nth calculation needs to be performed.

Calculating dense rank

For the employee table, the entire data gets divided based on the Dep_name column and ordered by the salary column.

The dense rank function will be applied to each partitioned data to calculate the highest salary.

select Dep_name,Emp_id,Salary,DENSE_RANK() OVER (PARTITION BY Dep_name ORDER BY Salary desc) as denserank from employee;

+----------+--------+--------+-----------+
| Dep_name | Emp_id | Salary | denserank |
+----------+--------+--------+-----------+
| Computer |    943 |   3200 |         1 |
| Computer |    123 |   2500 |         2 |
| Computer |    324 |   2500 |         2 |
| Computer |    564 |   1400 |         3 |
| Economy  |    789 |   7200 |         1 |
| Economy  |    678 |   6700 |         2 |
| Economy  |    456 |   4500 |         3 |
| History  |    987 |   3450 |         1 |
+----------+--------+--------+-----------+
8 rows in set (0.00 sec)

Third highest salary for each department

With the calculated dense rank value for each department, we could filter the third dense rank to get the third highest salary.

select a.Dep_name,a.Emp_id,a.Salary from (select Dep_name,Emp_id,Salary,DENSE_RANK() OVER (PARTITION BY Dep_name ORDER BY Salary desc) as denserank from employee) a where a.denserank=3;

+----------+--------+--------+
| Dep_name | Emp_id | Salary |
+----------+--------+--------+
| Computer |    564 |   1400 |
| Economy  |    456 |   4500 |
+----------+--------+--------+

Reference

https://www.mysqltutorial.org/mysql-window-functions/mysql-dense_rank-function/