One of the most common SQL interview questions is to find the Nth
highest salary of employee, 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 Interviewer keep asking about 4th highest, 5th
highest and so on. In order to solve this problem effectively, you need
to know about some key concepts like 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 by using LIMIT keyword in MySQL. Btw, if you are new to SQL and just learning these query from interviews sake then I suggest you to first read a good book on SQL e.g. Head First SQL. It will help you to build your fundamentals.
Use below query to create table and build schema:
One of the most common ways to solve this problem of finding the Nth maximum salary from 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:
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:
3rd highest salary:
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. Highest salary means no salary is higher than it, 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 database 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.
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 top most salary, which would be your Nth highest salary.
3rd highest salary in SQL SERVER
Here is another example where we have used the TOP keyword to find the second highest salary in Microsoft SQL SERVER 2008.
2nd highest salary in MySQL without subquery:
3rd highest salary in MySQL using LIMIT clause:
Nth highest salary in MySQL using LIMIT clause:
Explanation :
The benefit of this approach is that it's faster than correlated query approach but its vendor dependent. This solution will only work in MySQL database.
Here is the 2nd highest salary in Oracle using ROW_NUMBER() window function:
and here is 3rd highest salary in Oracle:
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:
In oracle, you can also use SQL statements to build schema and run sample SQL.
You can also do the same thing by using 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 become really nested and cluttered.
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 by using LIMIT keyword in MySQL. Btw, if you are new to SQL and just learning these query from interviews sake then I suggest you to first read a good book on SQL e.g. Head First SQL. It will help you to build your fundamentals.
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 employee table with some data.Use below query to create 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 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. Highest salary means no salary is higher than it, 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 database 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.
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 top most salary, which would be your Nth highest salary.
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 correlated query approach but its vendor dependent. This solution will only work in 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 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 become really nested and cluttered.
