Home » SQL

Find record of nth highest salary employee in SQL?

Finding record of Nth highest salaried employee (where N, should equal to records or less then of the records), Here we are finding 1st , 2nd, 3rd and so on highest salaried employee’s name one by one using SQL Query.

Here employee table, which has three fields eid(employee id), ename(employee name), salary(employee salary).

Eid	ename	Salary
101	preeti	20000
102	apoorv	40000
103	antima	50000
104	rahul	10000
105	pankaj	5000

Here, following queries being implemented:

1) Write a query to find name of 1^st highest salary employee from employee table?

mysql> select ename from employee 
where salary = (select max(salary) from employee);

Result:

Ename
Antima

2) Write a query to find name of 2^nd highest salary employee from employee table?

mysql> select ename from employee 
where salary = ( select max(salary) from employee 
where salary <( select max(salary) from employee));

Result:

Ename
Apoorv

3) Write a query to find name of 3^rd highest salary employee from employee table?

mysql> select ename from employee 
where salary = ( select max(salary) from employee 
where salary <( select max(salary) from employee 
where salary <( select max(salary) from employee)));

Result:

Ename
Preeti

4) Write a query to find name of 4^th highest salary employee from employee table?

mysql> select ename from employee 
where salary = ( select max(salary) from employee 
where salary <( select max(salary) from employee 
where salary <( select max(salary) from employee 
where salary < (select max(salary) from employee))));

Result:

Ename
rahul

5) Write a query to find name of 5^th highest salary employee from employee table?

mysql> select ename from employee 
where salary = ( select max(salary) from employee 
where salary <( select max(salary) from employee 
where salary <( select max(salary) from employee 
where salary < (select max(salary) from employee 
where salary< (select max(salary) from employee)))));

Result:

Ename
pankaj