The Nth Highest Salary Problem

Q: Why does LIMIT 2, 1 return the wrong result without DISTINCT?

Without DISTINCT, duplicate salaries take up row offsets, causing LIMIT to fetch another instance of the 2nd highest salary instead of the true 3rd highest.

Q: What is the difference between RANK() and DENSE_RANK() for this problem?

RANK() skips subsequent rank numbers after ties (e.g., 1, 2, 2, 4), whereas DENSE_RANK() maintains consecutive numbers (e.g., 1, 2, 2, 3).

Q: Which approach is most preferred in production and interviews?

DENSE_RANK() is preferred because it handles duplicates cleanly, scales to any N, and complies with modern SQL standards.

The most-asked SQL interview question of all time. Five wrong answers, two right ones. FIND_VIDEO: search 'nth highest salary SQL leetcode' — recommended channel: Joma Tech / Alex Freberg. Aim for 9 min or under.

12 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Problem Overview — Introduces the problem of finding the Nth highest salary and outlines solving it via five methods.
Nested MAX Approach — Demonstrates finding the 2nd and 3rd highest salaries using nested MAX subqueries.
LIMIT Clause and DISTINCT — Explains LIMIT offset syntax and why DISTINCT is necessary to handle duplicate salary rows.
Correlated Self-Join — Shows how to count strictly greater distinct salaries using correlated subqueries matching N-1.
DENSE_RANK Window Function — Illustrates ranking salaries with DENSE_RANK() inside a derived table to retrieve rank N.
NOT IN and Inequality Filter — Covers finding the second highest salary using MAX combined with NOT IN or not equal operators.

PDF notes

Bottom line: You will master five distinct SQL approaches to retrieve the Nth highest salary from an employee table, handling duplicate salaries and null values cleanly. You can apply these patterns in technical interviews to demonstrate both standard ANSI SQL knowledge and database-specific optimizations.

This lesson on The Nth Highest Salary Problem is hands-on and example-driven. You will master five distinct SQL approaches to retrieve the Nth highest salary from an employee table, handling duplicate salaries and null values cleanly. You can apply these patterns in technical interviews to demonstrate both standard ANSI SQL knowledge and database-specific optimizations.

What You'll Be Able To Do

Write nested subqueries with MAX to find top-tier ordinal salaries.
Implement LIMIT with OFFSET parameters alongside DISTINCT to eliminate duplicate rankings.
Construct correlated self-joins comparing row values to find relative rank.
Utilize DENSE_RANK() window functions inside derived tables to partition salary tiers.
Apply NOT IN and inequality operators for quick second-highest salary lookups.

Detailed Concept Walkthrough

1. Nested Aggregate MAX Approach

Finds the Nth highest salary by filtering out values equal to or greater than the maximums retrieved by nested inner subqueries.

Mechanism: An innermost subquery extracts the absolute MAX salary, while outer queries successively fetch the MAX salary that is strictly less than the subquery's result.
Under the Hood: Each nesting level adds another full table scan or index scan to resolve the scalar aggregate before filtering the next level up.
Best Practice: Use this method primarily for the 2nd highest salary; scaling beyond 3 levels causes deep query nesting that degrades maintainability and performance.

-- 2nd highest salary using nested MAX
SELECT MAX(salary) 
FROM employee_info 
WHERE salary < (
    SELECT MAX(salary) 
    FROM employee_info
);

Key Takeaway: Nested MAX queries work well for the 2nd highest value but become unmaintainable as N grows.

2. LIMIT and OFFSET with DISTINCT

Sorts distinct salaries in descending order and skips N-1 records to pinpoint the target value.

Mechanism: The query applies ORDER BY salary DESC with a LIMIT offset, count syntax (or LIMIT count OFFSET offset) to extract exactly one record after skipping N-1 rows.
Under the Hood: Without DISTINCT, identical salary values occupy multiple offset slots, causing the query to return duplicates instead of the true Nth highest tier.
Syntax Rule: The syntax LIMIT n-1, 1 skips n-1 records and retrieves the single next row representing the Nth distinct salary.

-- Nth highest salary using LIMIT (e.g., 3rd highest: LIMIT 2, 1)
SELECT DISTINCT salary 
FROM employee_info 
ORDER BY salary DESC 
LIMIT 2, 1;

Key Takeaway: Always include DISTINCT before ORDER BY and LIMIT to prevent duplicate values from consuming ordinal positions.

3. Correlated Self-Join Approach

Identifies a salary by counting how many distinct salaries in the same table are strictly greater than it.

Mechanism: An outer table alias e1 is compared against an inner alias e2, where the inner correlated subquery calculates COUNT(DISTINCT e2.salary) WHERE e2.salary > e1.salary.
Execution Flow: For the Nth highest salary, there must be exactly N-1 higher distinct values in the table; matching this condition filters the exact row.
Best Practice: This query uses standard ANSI SQL without proprietary pagination clauses, making it universally portable across all RDBMS engines.

-- 3rd highest salary using correlated subquery (N - 1 = 2)
SELECT e1.salary 
FROM employee_info e1 
WHERE 2 = (
    SELECT COUNT(DISTINCT e2.salary) 
    FROM employee_info e2 
    WHERE e2.salary > e1.salary
);

Key Takeaway: Correlated subqueries evaluate the count of strictly higher distinct salaries to match N-1 without dialect-specific pagination.

4. Window Functions with DENSE_RANK

Assigns contiguous rank integers to unique salary values in descending order and filters by rank in an outer query.

Mechanism: DENSE_RANK() OVER (ORDER BY salary DESC) assigns identical ranks to duplicate salaries without skipping subsequent ranks.
Under the Hood: Because window functions are evaluated after the WHERE clause, the ranking logic must execute inside an inline view or derived table.
Best Practice: Use DENSE_RANK instead of RANK or ROW_NUMBER to ensure tied salaries share a tier and the next distinct salary receives the consecutive integer.

-- Nth highest salary using DENSE_RANK (e.g., 3rd highest)
SELECT name, salary 
FROM (
    SELECT name, salary, 
           DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank 
    FROM employee_info
) AS temp 
WHERE salary_rank = 3;

Key Takeaway: DENSE_RANK guarantees consecutive integer rankings across duplicate values, making it the industry standard approach.

5. Filtering with NOT IN and Inequality

Excludes the top salary using NOT IN or != to find the next maximum value in a single step.

Mechanism: The subquery finds MAX(salary), and the outer query takes MAX(salary) WHERE salary NOT IN (subquery) or WHERE salary != (subquery).
Under the Hood: This functions identically to the nested MAX approach but uses membership/inequality operators instead of the < relational operator.
Nuance: This pattern is strictly limited to finding the 2nd highest value and does not generalize cleanly to arbitrary N.

-- 2nd highest salary using NOT IN
SELECT MAX(salary) 
FROM employee_info 
WHERE salary NOT IN (
    SELECT MAX(salary) 
    FROM employee_info
);

Key Takeaway: NOT IN and != provide a direct syntax alternative for the 2nd highest salary but fail to scale to Nth ranks.

Topics Covered in The Nth Highest Salary Problem

Problem Overview (0:00 - 0:50) — Introduces the problem of finding the Nth highest salary and outlines solving it via five methods.
Nested MAX Approach (0:50 - 2:10) — Demonstrates finding the 2nd and 3rd highest salaries using nested MAX subqueries.
LIMIT Clause and DISTINCT (2:10 - 4:15) — Explains LIMIT offset syntax and why DISTINCT is necessary to handle duplicate salary rows.
Correlated Self-Join (4:15 - 6:20) — Shows how to count strictly greater distinct salaries using correlated subqueries matching N-1.
DENSE_RANK Window Function (6:20 - 7:50) — Illustrates ranking salaries with DENSE_RANK() inside a derived table to retrieve rank N.
NOT IN and Inequality Filter (7:50 - 8:35) — Covers finding the second highest salary using MAX combined with NOT IN or not equal operators.

SQL Cheat Sheet

MAX() with Subquery — Fetches max salary below the inner subquery maximum

SELECT MAX(salary) FROM employee_info WHERE salary < (SELECT MAX(salary) FROM employee_info);

LIMIT offset, count — Skips offset rows and returns specified row count

SELECT DISTINCT salary FROM employee_info ORDER BY salary DESC LIMIT 1, 1;

Correlated COUNT(DISTINCT) — Counts rows with higher salaries per candidate row

SELECT salary FROM employee_info e1 WHERE (N-1) = (SELECT COUNT(DISTINCT e2.salary) FROM employee_info e2 WHERE e2.salary > e1.salary);

DENSE_RANK() OVER() — Assigns consecutive rank numbers without gaps for ties

SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM employee_info) t WHERE rnk = 3;

NOT IN / != with MAX() — Selects maximum salary excluding the overall maximum

SELECT MAX(salary) FROM employee_info WHERE salary != (SELECT MAX(salary) FROM employee_info);

Comparison Table

Approach	Scalability to N	Handles Duplicates
Nested MAX	Poor (requires N nested queries)	Yes (via aggregate MAX)
LIMIT / OFFSET	High (change offset to N-1)	Requires explicit DISTINCT
Correlated Subquery	High (parameterize N-1 count)	Requires COUNT(DISTINCT)
DENSE_RANK()	High (filter rank = N)	Native (handles ties seamlessly)
NOT IN / !=	None (2nd highest only)	Yes (via aggregate MAX)

Common Pitfalls

Mistake: Using LIMIT N-1, 1 without DISTINCT on tables with duplicate values. Avoid: Always apply DISTINCT before ORDER BY when using LIMIT for ordinal ranking.
Mistake: Using RANK() instead of DENSE_RANK() when duplicate salaries exist. Avoid: Use DENSE_RANK() to prevent duplicate values from skipping subsequent rank integers.
Mistake: Filtering window function results directly in the WHERE clause of the same query block. Avoid: Wrap the window function in a derived table or CTE before filtering rank.
Mistake: Relying on nested MAX or NOT IN queries for dynamic Nth rank calculations. Avoid: Use DENSE_RANK() or correlated subqueries for generalizable Nth rank queries.

FAQs

Why does LIMIT 2, 1 return the wrong result without DISTINCT? Without DISTINCT, duplicate salaries take up row offsets, causing LIMIT to fetch another instance of the 2nd highest salary instead of the true 3rd highest.
What is the difference between RANK() and DENSE_RANK() for this problem? RANK() skips subsequent rank numbers after ties (e.g., 1, 2, 2, 4), whereas DENSE_RANK() maintains consecutive numbers (e.g., 1, 2, 2, 3).
Which approach is most preferred in production and interviews? DENSE_RANK() is preferred because it handles duplicates cleanly, scales to any N, and complies with modern SQL standards.

Common pitfalls

Mistake: Using LIMIT N-1, 1 without DISTINCT on tables with duplicate values. Avoid: Always apply DISTINCT before ORDER BY when using LIMIT for ordinal ranking.
Mistake: Using RANK() instead of DENSE_RANK() when duplicate salaries exist. Avoid: Use DENSE_RANK() to prevent duplicate values from skipping subsequent rank integers.
Mistake: Filtering window function results directly in the WHERE clause of the same query block. Avoid: Wrap the window function in a derived table or CTE before filtering rank.
Mistake: Relying on nested MAX or NOT IN queries for dynamic Nth rank calculations. Avoid: Use DENSE_RANK() or correlated subqueries for generalizable Nth rank queries.

Hello guys, welcome to CodeEra. And I hope that you like the last uploaded video, which is top interview question on SQL. And if you didn't watch the video yet, so just click the i-card at the top right corner of the video screen. So guys, in today's video, we will discuss one of the most asked SQL query in interviews, that is, to find the nth highest salary of the employee. Here, nth means second highest, third highest, and so on. So guys, what's special about today's video? So, we will solve this query in five different ways. Because in interview, when you solve the query, the interviewer ask you to solve the same query in some other ways. So you must know some other ways too to solve this query. And if you tell the interview that you know how to solve this query in different ways, he will definitely will get impressed. So guys, let's start the today's video. So, in the complete video, we will use this employee info table, and our main focus is on the salary column. So here is our salary column, and I have arranged this in the descending order for better understanding. And you can see here we have some duplicate and null values also. Now in our first approach, we will use MAX function to find the nth highest salary. So let's see how to find the second highest salary. Here, observe this inner query, which is select MAX function from the employee info. So this query will fetch the highest salary from the employee info table, which is 5 lakh. But we need second highest salary, which is 3 lakh. So now the outer query, which is select MAX salary from the employee info where salary is less than the result of the inner query. So the inner query will return 5 lakh. The query will become select MAX salary from employee info where salary is less than 5 lakh. So this query will fetch maximum salary which is less than 5 lakh. So when we execute the query, we will get second highest salary, which is 3 lakh. So I hope this is clear to you. But what if we have to find the third highest salary using MAX function? So for this, we need to add one more inner query like this, which is exactly same as before. But here we just added one more inner query. So this innermost query will give us the highest salary, which is 5 lakh. And this inner query will give us the maximum salary less than 5 lakh, which is 3 lakh. Now the final query, which give us the maximum salary less than 3 lakh. So after the execution, we will get 80,000, which is third highest salary. But here you can see if we have to find the second highest salary, then this query is a good approach. But if we have to find the third, fourth, or fifth highest salary, then this query will become more complex. So, let's see another approach in which we will use LIMIT clause to solve the query. So first let's see how LIMIT clause works. So in LIMIT clause, we pass two parameters. Here, first parameter defines after how many rows it will start fetching records. Here, 3 means it will fetch record after third rows. And second parameter defines the number of rows it will fetch. Here, 4 means it will fetch 4 records. So here, LIMIT clause will fetch 4 records after third row. So I hope LIMIT clause is clear to you. So to get the second highest salary, we will write select salary from employee info order by salary DESC. Now, this query will arrange the salary column in descending order. And now we need second record for second highest salary. So we will write LIMIT 1,1. So it will fetch one record after the first row, that is second record, which is 3 lakh. So when we execute the query, we will get this output. You can see here one record after the first row is 3 lakh. So in general, we can write the query like this. Here, you can put any value of n according to your requirement. Like if you want to get the third highest salary, so you can write select salary from employee info order by salary descending LIMIT 3 - 1, 1, which is LIMIT 2,1. When you execute the query, you will get this output, which is again 3 lakh. But we know this output is wrong, because third highest salary is 80,000. Because here LIMIT clause will fetch one record after two rows, which is 3 lakh. So here the problem is that we have two same value or duplicate values. So to resolve this problem, we will use DISTINCT, which will ignore the duplicate values. So the query will become something like this: select distinct salary from employee info order by salary descending LIMIT n - 1, 1. So when we find the third highest salary using DISTINCT, we will get our desired output, which is 80,000. So I hope you understand this. Now, in the next approach, we will find the nth highest salary without using LIMIT clause. So basically in this query, we are using self join. Here, we will compare employee info table with itself. So we will write select salary from employee info e1. Here, e1 is the temporary name for the employee info table. And then we will write n - 1. Here, the value of n can be 1, 2, 3 or anything according to the highest salary you want to fetch. So for example, if we want third highest salary, this will be 3 - 1, that is 2. But why we are interested in 2? Because we are finding third highest salary. So obviously there will be two more higher salary than the third one. That's why we are interested in 2. When this WHERE condition will become true, we will get the desired result, that is third highest salary. So to make this condition true, let's understand the inner query, which is select count distinct salary from employee info e2 where e2.salary is greater than e1.salary. Here, we are using DISTINCT to avoid duplicate values. And employee info e2 is the same table with the different alias. So in inner query, we want to count the number of salary from e2 table where salary of e2 table is greater than salary of e1 table. If we get the count equal to 2, then that salary will be the third highest salary. So here we are comparing each row of e1 table to each row of e2 table. For example, 3 lakh. When we compare 3 lakh of e1 table to each row of e2 table, we will get count equal to 1, because there is only one salary which is greater than 3 lakh, that is 5 lakh. So the WHERE condition will be false as 2 is not equal to 1. Now let's check the correct output, that is 80,000. So when we compare 80,000 of e1 table to each row of e2 table, then we will get count equal to 2, as we have 5 lakh and 3 lakh which are greater than 80,000. And the condition in the outer query will become true as we get 2 equal to 2. So here, 80,000 is the third highest salary. So I hope you understand this query. If you still have any doubt, you can ask me in the comment box. Now let's see our next approach using DENSE_RANK function. So first let's understand what is DENSE_RANK function. So this function assigns rank to each row according to the partition and the order by clause. This function increase the rank by 1 for the distinct values. If there is any duplicate value, so this function will assign same rank for both the values. Here is the syntax for the DENSE_RANK function. We are not going so deep in this, let's jump to our problem statement. It will clear your all the doubts. So this is the query to find the nth highest salary using DENSE_RANK function. So first we will understand this inner query, which is select name, salary, and then we will write DENSE_RANK OVER. And inside OVER, we will specify the column name on which we want to apply rank with ORDER BY clause. So in our case, we want to apply rank on the salary column. And then we will write alias or temporary name for the DENSE_RANK result, that is salary rank. After that, we will write FROM employee info. So this query will arrange the result in the descending order according to the salary. And then it will also add a new column named salary rank to define the rank of the salary. So when we execute the query, we will get this output. Here you can see salary is in descending order, and we also have a column which has ranking respective to the salary. And here you can observe that for the same value, salary rank column has the same ranking. Now actually this result is the derived table, and the outer query will fetch the result according to this table. And here we will give a temporary name to the derived table as temp. So now the result of this inner query is stored in the temp. And you can imagine our query something like select star from temp where salary rank equal to 3. Here value of salary rank is equal to 3 because we want to find the third highest salary. Suppose you want to find second highest salary, so value of salary rank will be equal to 2. You find any highest salary according to your requirement. So this query will fetch the record from the temp table which is derived table where value of salary rank is equal to 3. So after the execution, we will get the third highest salary, that is 80,000. Now in our next approach, we will use NOT IN and not equal to operator. So first we will find the second highest salary. So again, in the inner query, we will find maximum or highest salary, which is 5 lakh in our case. So our query will be something like select MAX salary from employee info where salary not in 5 lakh. So the outer query will fetch the maximum salary from the table which is not equal to 5 lakh. So our output will be 3 lakh. Or at the place of NOT IN, you can also use not equal to operator to get the second highest salary. Both will give you the same result. But with the help of NOT IN or not equal to operator, you can only get the second highest salary. It will not work in the other cases. So guys, that's all about today's video. I hope you like the video and don't forget to subscribe our channel. Thank you so much for watching.

Frequently asked questions

Why does LIMIT 2, 1 return the wrong result without DISTINCT?