Find Nth Highest Salary in SQL

Kiran Kumar

1y1.1kSQL Server

Can we find Nth Highest Salary in SQL

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaish Mathews1y ago
Accepted answer
Its all scenario based.

Yes! You can find the Nth highest salary in SQL Server using several approaches. Here are the most common methods:

1. Using DISTINCT + ORDER BY + OFFSET

(SQL Server 2012+)
```
WITH SalaryRank AS (
    SELECT DISTINCT Salary 
    FROM Employees 
    ORDER BY Salary DESC 
    OFFSET (N-1) ROWS FETCH NEXT 1 ROWS ONLY
)
SELECT * FROM SalaryRank;
```
- Replace N with the desired rank.
- This method uses OFFSET to skip (N-1) rows and fetches the Nth highest salary.
2. Using TOP + DISTINCT + ORDER BY

(Works in older SQL Server versions too)
```
SELECT DISTINCT TOP 1 Salary 
FROM Employees 
WHERE Salary NOT IN (
    SELECT DISTINCT TOP (N-1) Salary FROM Employees ORDER BY Salary DESC
)
ORDER BY Salary DESC;
```
- This skips the top (N-1) salaries and fetches the next highest salary.
3. Using DENSE_RANK()

(Handles duplicate salaries properly)
```
WITH RankedSalaries AS (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
)
SELECT Salary FROM RankedSalaries WHERE rnk = N;
```
- DENSE_RANK() ensures that duplicate salaries don’t affect the ranking.
4. Using ROW_NUMBER()

(Useful when there are no duplicate salaries)
```
WITH RankedSalaries AS (
    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
)
SELECT Salary FROM RankedSalaries WHERE rnk = N;
```
- This works well when salaries are unique.
Which One Should You Use?
- If duplicates exist → Use DENSE_RANK().
- If no duplicates exist → ROW_NUMBER() is fine.
- If using SQL Server 2012+ and want a concise query → OFFSET FETCH is efficient.
Let me know if you need help with any specific scenario! ??
+2
Tuhin Paul1y ago
Part -2

Using Window Functions (DENSE_RANK) (SQL Server/Oracle/PostgreSQL)

Window functions like DENSE_RANK allow you to rank rows without collapsing them into a single result.
```
SELECT Salary
FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
) AS RankedSalaries
WHERE Rank = N;
```
- The inner query assigns a rank to each salary using DENSE_RANK(), ordering by salary in descending order.
- The outer query filters the results to return only the salary with the rank equal to N.
1. Full Table Scan
  The database reads all rows from the Employees table.
2. Sorting
  All salaries are sorted in descending order (ORDER BY Salary DESC).
3. Rank Assignment
  DENSE_RANK() assigns a rank to each row. For example:
```
Salary | Rank
-------------
200k   | 1
180k   | 2
180k   | 2  ? DENSE_RANK allows ties
150k   | 3
```
1. Filtering
  The outer query filters rows where Rank = N.
Why It's Inefficient for Large Datasets?
1. Full Sorting Overhead
  
  Sorting the entire dataset has O(n log n) time complexity.
  
  For a table with 1 million rows, this requires ~20 million operations.
2. No Short-Circuiting
  
  Even if you only need the 10th highest salary, all rows are processed.
  
  Example: For N=10, the database still sorts 1M rows.
3. Memory Pressure
  
  Sorting and ranking large datasets consume significant memory.
  
  May spill to disk if data exceeds available RAM.
4. Index Limitations
  
  Most databases cannot use indexes to optimize window functions like DENSE_RANK().
  
  Even with an index on Salary, the full ranking process is still required.
5. Redundant Work
  
  If multiple users request different N values, the entire ranking process repeats each time.
When to Use This Approach?
- Small datasets (<10k rows).
- When you need to handle tied salaries (e.g., two employees with the same salary at rank 2).
- Ad-hoc queries where efficiency is not critical.
For large datasets, prefer indexed subqueries or lateral joins to avoid full sorting. For example:
```
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET N-1;
```
This diagram is going to give you a deeper insights.
+1
Tuhin Paul1y ago
Part -1

Using LIMIT and OFFSET (MySQL/PostgreSQL)

This approach is straightforward and works well with databases that support LIMIT and OFFSET.
```
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET N-1;
```
- The query selects distinct salaries from the Employees table.
- It orders the salaries in descending order.
- LIMIT 1 ensures that only one record is returned.
- OFFSET N-1 skips the first N-1 records, effectively returning the Nth highest salary.
For a dataset with 1 million records, using LIMIT and OFFSET can become inefficient because:
- OFFSET N-1 forces the database to scan and sort many rows before reaching the Nth row.
- If N is large (e.g., OFFSET 1000000), it results in high disk I/O and memory usage.
- Sorting all salaries before selecting the Nth one is expensive.
Instead of using LIMIT and OFFSET, use a subquery with INDEX optimization:

1. Using a Subquery with DISTINCT and Index
```
SELECT Salary 
FROM Employees 
WHERE Salary >= (
    SELECT DISTINCT Salary 
    FROM Employees 
    ORDER BY Salary DESC 
    LIMIT N, 1
)
ORDER BY Salary ASC
LIMIT 1;
```
Using a Self-Join (More Efficient for Top-N)
```
SELECT DISTINCT e1.Salary
FROM Employees e1
JOIN Employees e2 ON e1.Salary <= e2.Salary
GROUP BY e1.Salary
HAVING COUNT(DISTINCT e2.Salary) = N;
```
Why is this better?
- It eliminates sorting overhead.
- Works well with indexed Salary columns.
- Avoids full table scans for large OFFSET values.
+1

Shubham Sidnale1y ago

1. Using TOP with DISTINCT and ORDER BY (Subquery)

SELECT DISTINCT Salary

FROM Employee

ORDER BY Salary DESC

OFFSET (N-1) ROWS FETCH NEXT 1 ROWS ONLY;

Replaces N with the required rank.
OFFSET (N-1) ROWS skips (N-1) rows.
FETCH NEXT 1 ROWS ONLY gets the N-th highest salary.
Works in SQL Server 2012+.

2. Using TOP with Subquery

SELECT TOP 1 Salary

FROM (SELECT DISTINCT TOP N Salary

FROM Employee

ORDER BY Salary DESC) AS Temp

ORDER BY Salary ASC;

Selects the top N salaries and then picks the lowest one (N-th highest).

3. Using DENSE_RANK() (Best for Handling Duplicates)

SELECT Salary

FROM (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk

FROM Employee) AS Ranked

WHERE rnk = N;

Assigns ranking without skipping duplicate values.
Best when salaries have duplicates.

4. Using ROW_NUMBER() (When Duplicates Should Be Ignored)

SELECT Salary

FROM (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rnk

FROM Employee) AS Ranked

WHERE rnk = N;

Assigns a unique row number.
If salaries are duplicated, the same salary may get different ranks.

5. Using CROSS APPLY (Efficient for Large Data)

SELECT Salary

FROM Employee E1

CROSS APPLY (SELECT COUNT(DISTINCT Salary) AS Rank

FROM Employee E2

WHERE E2.Salary >= E1.Salary) AS T

WHERE T.Rank = N;

Counts how many distinct salaries are greater than or equal to the current row’s salary.

6. Using OFFSET FETCH (Modern Approach)

SELECT Salary

FROM Employee

ORDER BY Salary DESC

OFFSET N-1 ROWS FETCH NEXT 1 ROW ONLY;

Introduced in SQL Server 2012+.
OFFSET N-1 skips (N-1) salaries.

Choosing the Best Method:

Method	Handles Duplicates?	Performance
TOP with DISTINCT	Yes	Fast for small datasets
DENSE_RANK()	Yes	Best for handling duplicate salaries
ROW_NUMBER()	No	Best when uniqueness is required
CROSS APPLY	Yes	Good for large datasets
OFFSET FETCH	Yes	Modern approach, efficient

Which one to use depends on your requirements (handling duplicates) and SQL Server version.