Finding the nth highest salary is one of the most popular SQL interview questions, yet it teaches concepts that go far beyond memorizing a query. Consider this: whether you are preparing for a data analyst role, sharpening your database skills, or simply curious about how relational databases rank and filter records, understanding how to retrieve the second, third, or nth highest salary from a table builds a strong foundation in subqueries, aggregate functions, and window functions. This guide walks you through multiple approaches, explains why each works, and shows you how to handle the tricky edge cases that separate a beginner answer from an expert one Easy to understand, harder to ignore..
Understanding the Problem
Before writing any query, it helps to define exactly what "nth highest salary" means. Suppose you have an employees table like this:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 70000),
(3, 'Charlie', 70000),
(4, 'David', 90000),
(5, 'Eve', 60000);
The highest salary here is 90,000. Now, notice that Bob and Charlie both earn 70,000. Here's the thing — the second highest is 70,000, and the third highest is 60,000. This raises an important question: when two employees share the same salary, do we count that value once or twice? The answer depends on whether you use dense ranking or distinct value ranking, and choosing the right method is the heart of solving this problem correctly Worth keeping that in mind..
Not obvious, but once you see it — you'll see it everywhere.
The Core Approaches
When it comes to this, several ways stand out. Each has trade-offs in terms of readability, performance, and database compatibility Nothing fancy..
Method 1: Using a Subquery with COUNT
A classic technique that works across almost every database engine uses a correlated subquery. The idea is elegant: for each salary, count how many distinct salaries are greater than or equal to it. If exactly n distinct salaries are greater than or equal to a value, that value is the nth highest.
SELECT DISTINCT salary
FROM employees e1
WHERE (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary >= e1.salary
) = 2;
To find the second highest salary, we set the count equal to 2. To make it dynamic for any n, you would replace 2 with your desired rank. This method is portable and works in MySQL, PostgreSQL, SQL Server, and Oracle, which makes it a favorite in interviews where the database engine is unspecified.
Method 2: Using ORDER BY with LIMIT and OFFSET
If you know the specific database you are working with, ordering the salaries and skipping rows is often the simplest solution. In MySQL and PostgreSQL, you can write:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Here OFFSET 1 skips the highest salary, and LIMIT 1 returns the next one, giving you the second highest. Practically speaking, for the nth highest, use OFFSET n-1. In SQL Server, the equivalent uses `OFFSET .. And that's really what it comes down to..
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY;
This approach is clean and fast, but it is database-specific and less flexible when n is a variable rather than a fixed number.
Method 3: Using Window Functions (The Modern Approach)
The most powerful and readable solution uses window functions, specifically DENSE_RANK(). A window function assigns a rank to each row based on the ordering, and DENSE_RANK() handles duplicate values gracefully by giving them the same rank without skipping numbers.
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees
) ranked
WHERE rank_num = 2;
In this query, the inner subquery assigns ranks: 90,000 gets rank 1, both 70,000 values get rank 2, and 60,000 gets rank 3. The outer query then filters for rank_num = 2, returning the second highest salary. To find the nth highest, simply change the filter value.
Choosing Between DENSE_RANK, RANK, and ROW_NUMBER
This is where many candidates get caught. SQL offers three similar ranking functions, and they behave differently with duplicate values:
ROW_NUMBER()assigns a unique sequential number to every row, even duplicates. Two employees earning 70,000 would get ranks 2 and 3, which means the "third highest" would incorrectly become 60,000.RANK()gives duplicates the same rank but skips subsequent numbers. Two salaries of 70,000 both get rank 2, and the next value jumps to rank 4, leaving a gap.DENSE_RANK()gives duplicates the same rank and does not skip numbers, making it the correct choice for finding distinct nth highest salaries.
For the nth highest salary problem, DENSE_RANK() is almost always the right answer because it treats tied salaries as a single rank, matching the intuitive meaning of "distinct nth highest value."
Handling Edge Cases
A strong solution must account for situations where the nth highest salary does not exist. If a table has only three employees and you ask for the fifth highest salary, the query should return NULL rather than an error.
One common pattern wraps the query to guarantee a single result:
SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
This cleverly finds the highest salary that is strictly less than the maximum, which is the second highest. In real terms, if no such value exists, MAX() returns NULL, giving a clean, predictable result. This technique is especially popular on coding platforms because it always returns exactly one row But it adds up..
Performance Considerations
When working with large tables, performance matters. The correlated subquery approach can be slow because it effectively scans the table for every row, giving it a time complexity close to O(n²). The window function approach is generally more efficient, as the database can sort the data once and assign ranks in a single pass, resulting in O(n log n) performance due to the sort.
The official docs gloss over this. That's a mistake.
To optimize further, ensure the salary column is indexed. An index allows the database engine to retrieve ordered values quickly without a full table scan, which can dramatically reduce query time on tables with millions of rows.
Frequently Asked Questions
What is the difference between the nth highest and nth lowest salary?
The nth highest salary uses ORDER BY salary DESC, while the nth lowest uses ORDER BY salary ASC. The logic is otherwise identical; you simply reverse the sort direction.
Can I find the nth highest salary without window functions?
Yes. The correlated subquery with COUNT(DISTINCT salary) and the LIMIT ... OFFSET methods both work without window functions, which is useful for older database versions that do not
Can I find the nth highest salary without window functions?
Yes, there are a couple of classic approaches that work in SQL dialects that do not support window functions (e.g., MySQL < 8.0, older PostgreSQL). The most common is the correlated subquery technique:
SELECT DISTINCT salary
FROM employees e1
WHERE (SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary >= e1.salary) = :n;
This query counts how many distinct salaries are greater than or equal to the current salary and keeps only those where that count equals the desired rank n. It works but can be inefficient on large tables because it evaluates a subquery for every row.
Not the most exciting part, but easily the most useful It's one of those things that adds up..
Another approach uses LIMIT/OFFSET when the table is ordered by salary descending:
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET :n-1;
This returns the n‑th row after sorting. It is simple and often fast, but it does not handle ties correctly—if two employees share the same salary, they occupy separate rows and the offset may skip over the duplicate salary, giving a result that is not the distinct n‑th highest. To fix this, you can first deduplicate with DISTINCT or use a subquery that removes duplicates before applying the offset.
For databases that support subqueries with ROW_NUMBER() (a window function) but not DENSE_RANK(), you can emulate dense ranking by using ROW_NUMBER() over PARTITION BY salary tricks, though this adds complexity.
To keep it short, while window functions like DENSE_RANK() provide the cleanest and most performant solution, the correlated subquery and LIMIT/OFFSET patterns remain valuable alternatives when you must support older SQL dialects or when the dataset is small enough that performance is not a concern.
Additional FAQ
Additional FAQ
How does DENSE_RANK() differ from ROW_NUMBER() when calculating the nth highest salary?
DENSE_RANK() assigns the same rank to rows that share a value and then continues the count without gaps. To give you an idea, with salaries [100, 90, 90, 80] the dense ranks would be 1, 2, 2, 3. In contrast, ROW_NUMBER() gives each row a unique sequential number, producing 1, 2, 3, 4 even when the values are tied. As a result, DENSE_RANK() is more appropriate when you need to treat ties as a single rank, while ROW_NUMBER() is useful when you must break ties arbitrarily Easy to understand, harder to ignore. Surprisingly effective..
Can I use a CTE (Common Table Expression) to simplify the nth‑highest query?
Yes. A CTE lets you break the logic into clear steps, which improves readability and can make the query easier to maintain. For instance:
WITH ranked AS (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees
)
SELECT salary
FROM ranked
WHERE dr = :n;
The CTE first creates a ranked set and then filters on the desired rank. This pattern works in any modern SQL engine that supports window functions.
What if my table is partitioned by a column that is not the sorting key?
If you partition by a column unrelated to the sort order (e.g., department), the database must still scan the entire partition before applying the window function, which can be costly. To mitigate this, you can create a materialized view or a pre‑aggregated table that already contains the distinct salaries per partition, then query that structure. Another option is to use a range‑based index on (salary DESC) that aligns with the partition key, allowing the optimizer to locate the needed rows without a full table scan.
How do I handle very large result sets when the nth‑highest rank is close to the top?
For extreme cases where n is large (e.g., the 99th highest salary in a billion‑row table), a simple ORDER BY with LIMIT may still be slow because it has to sort the whole dataset. In such scenarios, consider:
- Using a bitmap index or covering index on
salaryto reduce the number of rows examined. - Applying a filter that narrows the candidate set before ranking, for example by limiting to a specific
departmentordate range. - Leveraging parallel execution if your DBMS supports it (many platforms can scan partitions concurrently).
Is there a way to get the nth highest salary including ties?
If you want the salary value that appears at least n times when sorted descending, you can use COUNT(*) in a subquery:
SELECT salary
FROM employees
WHERE salary <= (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT n - 1
);
This returns the salary that is at or below the n‑th distinct value, effectively giving you the n‑th highest distinct salary while preserving ties Worth keeping that in mind..
What about performance tuning for the correlated‑subquery approach on large tables?
If you must use the correlated subquery for compatibility reasons, you can improve its speed by adding a filter on the inner query:
SELECT DISTINCT salary
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM employees e2
WHERE e2.salary >= e1.salary
AND e2.id <> e1.id -- exclude self‑match to avoid counting the same row
);
The EXISTS clause stops as soon as a matching row is found, reducing the number of comparisons. Additionally, creating an index on salary (both ascending and descending) can help the optimizer locate qualifying rows faster.
Conclusion
Understanding how to retrieve the n‑th highest (or lowest) salary is a common requirement in analytical workloads, and the toolbox for solving it is broader than just window functions. Correlated subqueries and LIMIT/OFFSET provide simple, portable solutions, while modern window functions such as DENSE_RANK() and ROW_NUMBER() deliver cleaner, often more performant code when the database architecture permits. Which means by choosing the right technique—taking into account data size, index availability, and query engine capabilities—you can check that ranking operations remain fast and scalable, even as the underlying tables grow to millions (or billions) of rows. The key is to match the approach to the specific constraints of your environment, and to keep an eye on query execution plans to verify that the chosen method is indeed meeting performance goals Took long enough..