4 Ways To Honor Nth Highest Salary Inward Sql - Oracle, Mssql In Addition To Mysql

One of the most mutual SQL interview questions is to abide by the Nth highest salary of employee, where north could hold out 2, 3, iv or anything e.g. abide by the second highest salary inwards SQL. Sometimes this query is every bit good twisted every bit to abide by the nth minimum salary inwards SQL. Since many Programmers alone know the tardily way to solve this job e.g. yesteryear using SQL IN clause, which doesn't scale well, they handle to write the SQL query when Interviewer drib dead on squall for close quaternary highest, fifth highest in addition to hence on. In lodge to solve this job effectively, y'all demand to know close roughly key concepts similar correlated subquery, window functions similar ROW_NUMER(), RANK() in addition to DENSE_RANK() etc. Once y'all know the generic logic to solve this problem, y'all tin tackle all those variations yesteryear yourself.


In this article, I'll exhibit y'all iv ways to solve this job e.g. yesteryear using the correlated subquery, using ROW_NUMBER(), using TOP inwards SQL SERVER in addition to yesteryear using LIMIT keyword inwards MySQL. Btw, if y'all are novel to SQL in addition to simply learning these query from interviews sake hence I propose y'all to get-go read a adept majority on SQL e.g. Head First SQL. It volition aid y'all to construct your fundamentals.

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

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

Use below query to practise tabular array in addition to construct 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 job of finding the Nth maximum salary from Employee tabular array is yesteryear 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 yesteryear the primary query.  It's irksome but it tin solve problems which are hard to solve otherwise. Let's come across the SQL query to abide by 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 y'all tin supplant north amongst 2, and for tertiary maximum supplant 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: mention 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: mention salary John 4000

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

Pros :
1) The generic solution 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 yesteryear 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 occupation the TOP keyword to abide by the Nth highest salary inwards SQL SERVER. This is every bit good 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 take duplicates. The outer query volition hence selection the transcend most salary, which would hold out your Nth highest salary.

tertiary 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 event where nosotros convey used the TOP keyword to abide by the minute highest salary inwards Microsoft SQL SERVER 2008.

Nth maximum salary inwards MySQL using LIMIT keyword

Similar to TOP, MySQL every bit good supports a LIMIT keyword, which provides pagination capability. You tin abide by the nth highest salary inwards MySQL without using subquery every bit 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

tertiary 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 practise 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 tertiary 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 upwardly code has a problem. It is non treatment duplicate salaries properly. For example, inwards our tabular array nosotros convey 2 employees amongst salary 3000, that's our quaternary highest salary, but higher upwardly code volition impress the same salary, albeit dissimilar employee for both quaternary in addition to fifth maximum every bit 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, y'all tin every bit good occupation SQL statements to construct schema in addition to run sample SQL.

You tin every bit good practise the same matter yesteryear using RANK() window business office inwards Oracle, but that's for roughly 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 close how to abide by the nth highest salary inwards SQL. The easiest way to abide by nth maximum/minimum salary is yesteryear using the correlated subquery, but it's non the fastest way. Better ways are database theme e.g. y'all 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 adept for the minute maximum but subsequently that, it acquire 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 abide by 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