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 1st 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 2nd 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 3rd 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 4th 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 5th 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





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.



Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.