4 Ways To Honour Nth Highest Salary Inwards Sql - Oracle, Mssql As Well As Mysql

One of the most mutual SQL interview questions is to honor the Nth highest salary of employee, where north could hold out 2, 3, four or anything e.g. honor the second highest salary inwards SQL. Sometimes this interrogation is too twisted equally to honor the nth minimum salary inwards SQL. Since many Programmers alone know the slow way to solve this work e.g. past times using SQL IN clause, which doesn't scale well, they fighting to write the SQL query when Interviewer proceed bespeak well-nigh quaternary highest, fifth highest in addition to thence on. In lodge to solve this work effectively, you lot demand to know well-nigh around key concepts similar correlated subquery, window functions similar ROW_NUMER(), RANK() in addition to DENSE_RANK() etc. Once you lot know the generic logic to solve this problem, you lot tin tackle all those variations past times yourself.


In this article, I'll exhibit you lot four ways to solve this work e.g. past times using the correlated subquery, using ROW_NUMBER(), using TOP inwards SQL SERVER in addition to past times using LIMIT keyword inwards MySQL. Btw, if you lot are novel to SQL in addition to simply learning these query from interviews sake thence I propose you lot to start read a practiced majority on SQL e.g. Head First SQL. It volition assistance you lot to ready your fundamentals.

Sample tabular array in addition to information for Nth Highest Salary Problem

Before solving this work nosotros demand around sample information to visualize the work better, let's create employee tabular array amongst around data.

Use below query to do tabular array in addition to ready schema:

-- creating Employee tabular array inwards Oracle CREATE TABLE Employee (name varchar(10), salary int);  -- inserting sample information 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

One of the most mutual ways to solve this work of finding the Nth maximum salary from Employee tabular array is past times using the correlated subquery. This is a exceptional type of subquery where the subquery depends upon the primary query in addition to execute for every row returned past times the primary query.  It's deadening but it tin solve problems which are hard to solve otherwise. Let's come across the SQL query to honor 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 lot tin supervene upon north amongst 2, and for third maximum supervene upon north amongst 3, hither 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: cite 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: cite salary John 4000

Explanation :
The distinct keyword is at that spot to bargain amongst duplicate salaries inwards the table. In lodge to honor the Nth highest salary, nosotros are alone considering unique salaries. Highest salary agency no salary is higher than it, Second highest agency alone ane salary is higher than it, third highest agency ii salaries are higher than it, similarly Nth highest salary agency N-1 salaries are higher than it.

Pros :
1) The generic solution industrial plant inwards all database including Oracle, MySQL, SQL SERVER in addition to PostgreSQL.

Cons :
1) Slow, because the inner query volition run for every row processed past times the outer query.

See SQL Puzzles in addition to Answers book for to a greater extent than of such SQL queries for practicing in addition to improving your SQL query skill.

Nth highest salary inwards SQL SERVER using TOP keyword

You tin usage the TOP keyword to honor the Nth highest salary inwards SQL SERVER. This is too faster than the previous solution because hither nosotros are calculating Nth maximum salary without a subquery.

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

Explanation: 
By default ORDER BY clause impress rows inwards ascending order, since nosotros demand the highest salary at the top, nosotros convey used ORDER BY DESC, which volition display salaries inwards descending order. Again DISTINCT is used to withdraw duplicates. The outer query volition thence alternative the transcend most salary, which would hold out your Nth highest salary.

third highest salary inwards 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 instance where nosotros convey used the TOP keyword to honor the minute highest salary inwards Microsoft SQL SERVER 2008.

Nth maximum salary inwards MySQL using LIMIT keyword

Similar to TOP, MySQL too supports a LIMIT keyword, which provides pagination capability. You tin honor the nth highest salary inwards MySQL without using subquery equally shown below:

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

2nd highest salary inwards MySQL without subquery:

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

third highest salary inwards MySQL using LIMIT clause:

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

Nth highest salary inwards MySQL using LIMIT clause:

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

Explanation :
The do goodness of this approach is that it's faster than correlated query approach but its vendor dependent. This solution volition alone piece of work inwards MySQL database.

Nth highest salary inwards 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 inwards 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

in addition to hither is third highest salary inwards Oracle:

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

By the higher upwards code has a problem. It is non treatment duplicate salaries properly. For example, inwards our tabular array nosotros convey ii employees amongst salary 3000, that's our quaternary highest salary, but higher upwards code volition impress the same salary, albeit dissimilar employee for both quaternary in addition to fifth maximum equally 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 lot tin too usage SQL statements to ready schema in addition to run sample SQL.

You tin too do the same matter past times using RANK() window business office inwards Oracle, but that's for around other day. This is to a greater extent than than plenty to reply the SQL interview question, the impress nth highest salary of an employee inwards the Oracle.


That's all well-nigh how to honor the nth highest salary inwards SQL. The easiest way to honor nth maximum/minimum salary is past times using the correlated subquery, but it's non the fastest way. Better ways are database subject e.g. you lot crusade TOP keyword inwards SQL SERVER, LIMIT keyword inwards MySQL in addition to ROW_NUMBER() window business office inwards Oracle to calculate the nth highest salary. The normal subquery way is practiced for the minute maximum but later on that, it boot the bucket actually nested in addition to cluttered.

Further Learning
read here)

What is the departure betwixt View in addition to Materialized View inwards Oracle database? (answer)

A departure betwixt UNION in addition to UNION ALL inwards SQL? (answer)

A departure betwixt LEFT in addition to RIGHT OUTER JOIN inwards SQL? (answer)

A departure betwixt self in addition to equijoin inwards SQL? (answer)

The departure betwixt WHERE in addition to HAVING clause inwards SQL? (answer)

How to honor duplicate records inwards a table? (query)

The departure betwixt TRUNCATE in addition to DELETE inwards SQL? (answer)

What is the departure betwixt Primary in addition to Foreign key inwards a table? (answer)

Share this post