Nth Highest Salary in SQL

Query to find Nth Highest Salary in Oracle and MYSQL 

The nth Highest Salary in SQL is one of the most frequently asked questions in tech interviews. You are given a table of employees with name details and salary. you have to find the Nth highest salary of an employee from the table. N can be any natural number. A question can also be asked to find Nth Minimum salary in from a table. But if you know how to find Nth highest salary then you can also find Nth lowest salary with the same logic.

Let us see how we can implement the query of this question if N is small.

Querry to Find Highest salary

--Highest Salary in SQL

select max(salary) from Employees

Second Highest Salary in SQL

The second Highest salary will be the maximum salary if we exclude the first highest salary from our table, now we will implement the same logic here.

--2nd Highest Salary in SQL

select max(salary) from Employees where salary<(select max(salary) from employees)

Explanation-: As we have already discussed that we can exclude the highest salary and then we can find the second highest salary.

Third Highest Salary in SQL

Similar way we can find the 3rd highest salary let us see the query

--3rd Highest Salary in SQL

select max(salary) from Employees where salary<(select max(salary) from employees where salary<(select max(salary) from employees) )

 

Explanation:- To find the 3rd highest salary I excluded the first highest salary and the second highest salary.

If we take any large value of N then the query will become very large in this way that's why we have to find an efficient and less time-consuming query to find Nth highest salary.

Nth Highest salary using Top keyword

-- Nth Highest Salary using Top keyword in SQL

select * from Employee where salary = ( select min( salary ) from Employee   

 where  salary IN (select distinct TOP N                                    

 salary from Employee order by salary desc ))  

We selected the top N rows with the highest salary and took minimum from that.

Also Read -[ SQL interview Questions ]

Nth Highest salary using Rownum

-- Nth Highest salary using Rownum

select min(salary) from   

         (select distinct salary from Employee order by salary desc)

                    where rownum <N+1;

Explanation -: we sorted all the salaries of employees and then selected salary from nth row from first. If we have to find the third highest salary then N would be 4 and so on.

In a similar way, we can find the Nth lowest salary in a database only we have to use the min function in place of max or vice versa.

The nth lowest salary in SQL

SELECT * FROM Employee A 

    WHERE  N-1= (SELECT COUNT(DISTINCT Salary) 

       FROM Employee B WHERE A.Salary < B.Salary)  

You can find Nth highest salary by replacing N with your required value You can explore some more ways to find Nth highest and Nth lowest salary such as by using dense_rank() function. You can learn the basics of SQL from us. Get Your all concept cleared only at courpedia related to SQL and other programming stuff.