Saturday, January 12, 2019

4 Ways to find Nth highest salary in SQL - Oracle, MSSQL and MySQL

One of the most common SQL interview questions is to find the Nth highest salary of employees, where N could be 2, 3, 4 or anything e.g. find the second highest salary in SQL. Sometimes this question is also twisted as to find the nth minimum salary in SQL. Since many Programmers only know the easy way to solve this problem e.g. by using SQL IN clause, which doesn't scale well, they struggle to write the SQL query when the Interviewer keeps asking about the 4th highest, 5th highest and so on. In order to solve this problem effectively, you need to know about some key concepts like a correlated subquery, window functions like ROW_NUMER(), RANK(), and DENSE_RANK(), etc. Once you know the generic logic to solve this problem, you can tackle all those variations by yourself.

In this article, I'll show you 4 ways to solve this problem e.g. by using the correlated subquery, using ROW_NUMBER(), using TOP in SQL SERVER, and using LIMIT keyword in MySQL.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It''s a great course to start with T-SQL and SQL queries in SQL Server.









Sample table and data for Nth Highest Salary Problem

Before solving this problem we need some sample data to visualize the problem better, let's create an employee table with some data.

Use below query to create a table and build schema:

-- creating Employee table in Oracle
CREATE TABLE Employee (name varchar(10), salary int);

-- inserting sample data into Employee table
INSERT INTO Employee VALUES ('Rick', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Shane', 3000);
INSERT INTO Employee VALUES ('Peter', 5000);
INSERT INTO Employee VALUES ('Jackob', 7000);



Nth highest salary using a correlated subquery

How to find Nth Highest Salary in SQL
One of the most common ways to solve this problem of finding the Nth maximum salary from the Employee table is by using the correlated subquery. This is a special type of subquery where the subquery depends upon the main query and execute for every row returned by the main query.  It's slow but it can solve problems which are difficult to solve otherwise. Let's see the SQL query to find the Nth highest salary using the Correlated subquery.

SQL Query:


SELECT name, salary 
FROM #Employee e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)

for the 2nd maximum you can replace N with 2, and for 3rd maximum replace N with 3, here is the output:

2nd highest salary:


SELECT name, salary 
FROM #Employee e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)SELECT name, salary 
FROM #Employee e1
WHERE 2-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)

Result:
name salary
Peter 5000


3rd highest salary:


SELECT name, salary 
FROM #Employee e1
WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)

Result:
name salary
John 4000

Explanation :

The distinct keyword is there to deal with duplicate salaries in the table. In order to find the Nth highest salary, we are only considering unique salaries. The highest salary means no salary is higher than it, the Second highest means only one salary is higher than it, 3rd highest means two salaries are higher than it, similarly Nth highest salary means N-1 salaries are higher than it.

Pros :
1) The generic solution works in all databases including Oracle, MySQL, SQL SERVER, and PostgreSQL.

Cons :
1) Slow, because the inner query will run for every row processed by the outer query.

See SQL Puzzles and Answers book for more of such SQL queries for practicing and improving your SQL query skill.




The nth highest salary in SQL SERVER using TOP keyword

You can use the TOP keyword to find the Nth highest salary in SQL SERVER. This is also faster than the previous solution because here we are calculating Nth maximum salary without a subquery.

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP N salary
FROM #Employee
ORDER BY salary DESC
) AS temp
ORDER BY salary

Explanation: 

By default ORDER BY clause print rows in ascending order, since we need the highest salary at the top, we have used ORDER BY DESC, which will display salaries in descending order. Again DISTINCT is used to remove duplicates. The outer query will then pick the topmost salary, which would be your Nth highest salary.

And, if you like books and just learning these queries from the interview's sake then I suggest you first read a good book on SQL like Head First SQL. It will help you to build your fundamentals.

3rd highest salary in SQL SERVER

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM #Employee
ORDER BY salary DESC
) AS temp
ORDER BY salary

Result:
salary
4000


Here is another example where we have used the TOP keyword to find the second highest salary in Microsoft SQL SERVER 2008.


Nth maximum salary in MySQL using LIMIT keyword

Similar to TOP, MySQL also supports a LIMIT keyword, which provides pagination capability. You can find the nth highest salary in MySQL without using subquery as shown below:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1


2nd highest salary in MySQL without subquery:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1
salary
5000

3rd highest salary in MySQL using LIMIT clause:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2,1
salary
4000

Nth highest salary in MySQL using LIMIT clause:

SELECT salary FROM Employee  ORDER BY Salary DESC LIMIT n-1,1

Explanation :
The benefit of this approach is that it's faster than a correlated query approach but its vendor dependent. This solution will only work in a MySQL database.



Nth highest salary in Oracle using ROW_NUMBER() function


SELECT * FROM (
SELECT e.*, 
ROW_NUMBER() OVER (ORDER BY salary DESC) rn 
FROM Employee e
)
WHERE rn = N; /*N is the nth highest salary*/


Here is the 2nd highest salary in Oracle using ROW_NUMBER() window function:

SELECT * FROM (
SELECT e.*, 
ROW_NUMBER() OVER (ORDER BY salary DESC) rn 
FROM Employee e
)
WHERE rn = 2; 

Output
NAME   SALARY  RN
Peter   5000   2

and here is 3rd highest salary in Oracle:

SELECT * FROM (
SELECT e.*, 
ROW_NUMBER() OVER (ORDER BY salary DESC) rn 
FROM Employee e
)
WHERE rn = 3; 


By the above code has a problem. It is not handling duplicate salaries properly. For example, in our table we have two employees with salary 3000, that's our 4th highest salary, but above code will print the same salary, albeit different employee for both 4th and 5th maximum as shown below:

SELECT * FROM (
SELECT e.*, 
ROW_NUMBER() OVER (ORDER BY salary DESC) rn 
FROM Employee e
)
WHERE rn = 5;

Result: 
NAME    SALARY  RN
Shane   3000    5

In oracle, you can also use SQL statements to build schema and run sample SQL.

You can also do the same thing by using the RANK() window function in Oracle, but that's for another day. This is more than enough to answer the SQL interview question, the print nth highest salary of an employee in the Oracle.


That's all about how to find the nth highest salary in SQL. The easiest way to find nth maximum/minimum salary is by using the correlated subquery, but it's not the fastest way. Better ways are database dependent e.g. you cause TOP keyword in SQL SERVER, LIMIT keyword in MySQL, and ROW_NUMBER() window function in Oracle to calculate the nth highest salary. The normal subquery way is good for the second maximum but after that, it becomes really nested and cluttered.


Related SQL Queries and Interview Questions
  • 21 Frequently asked SQL Queries from Interviews (read here)
  • What is the difference between View and Materialized View in the Oracle database? (answer)
  • A difference between UNION and UNION ALL in SQL? (answer)
  • A difference between LEFT and RIGHT OUTER JOIN in SQL? (answer)
  • A difference between self and equijoin in SQL? (answer)
  • The difference between WHERE and HAVING clause in SQL? (answer)
  • How to find duplicate records in a table? (query)
  • The difference between TRUNCATE and DELETE in SQL? (answer)
  • What is the difference between Primary and Foreign key in a table? (answer)

24 comments :

Anonymous said...

Great article, thanks for the clear and easy explanations of the various database scenarios with results!

silentEmotions said...

How can we overcome the limitation of row_number() showing same salary for 4th and 5th value of N ?

Jonee said...

You can also use row_number() in SQL Server. As its one of the window functions (not a reference to the OS) along with Rank, Dense_Rank

javin paul said...

@slientEmotions, use distinct keyword to only deal with unique salaries.

Anonymous said...

Will LIMIT in MySQL handle duplicates.

Unknown said...

by using Dense_Rank() we can implement nth highest salary instead of Row_Number() without duplicates

Praveen Singh said...

Nice article

javin paul said...

@Praveen, glad that you like this article about Nth highest salary, don't forget to share with your friends, it makes a lot of difference.

Anonymous said...

How can we handle the scenario in which everyone's salary is same?

Unknown said...

Easiest way to understand the queries. Thank you😃

Unknown said...

one more way:

select min(salary) from(select distinct salary from employee order by salary desc) where rownum <=3;
--this gives out the third highest salary

Unknown said...

Please make you website responsive for mobile device. For full sal query I have scroll left or right and when I tried it. It take to another page.
Overall for query good ans.

Unknown said...

SELECT min(CustomerID) FROM (SELECT distinct CustomerID from [orders] order by CustomerID desc limit 10 );

Shrads said...

Can you plz explain with example logic behind correlated query

Arun Singh said...

thanks

Unknown said...

What about this query

to get third highest salary

select min(salary) from (select salary,rownum from employee order by salary desc )where rownum < 4;

Anonymous said...

didnt exactly understand how the correlate subquery works. in the where condition it comes as 1=2 ( for 2nd highest salary) please give explaination of how it actually works

javin paul said...

Hello @Anonymous, in correlated subquery, for each row of outer query, all rows of inner query is compared. For second highest salary there will be only one salary which should be higher that's why that condition.

Shekh Iklakh said...

nth highest salary -

select * from table order by Salary desc offset n-1 rows fetch next 1 row only

javin paul said...

Hello Shekh, this SQL is for which database?

Sudhakar Yadav said...

Correlation query is wrong, we need to use "<" instead for ">" to find the 2nd,3rd,N-1 highest salary

SELECT name, salary
FROM Employee e1
WHERE 3-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary < e1.salary)

Pavan said...

In sql server using Row_number doesnt work for example if we have
another 4000 salary

You should make use of dense_rank

Shanky said...

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM #Employee
ORDER BY salary DESC
) AS temp
ORDER BY salary
Has syntax error, can you please resolve the same?

Pavan said...

you can use Dense_rank instead of row_number which will not skip the ranking in case o duplicates

Post a Comment