Find Nth Highest Salary in SQL

Question

Can we find Nth Highest Salary in SQL

Jaish Mathews · 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! ??

Tuhin Paul · Answer

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 . Full Table Scan The database reads all rows from the Employees table. Sorting All salaries are sorted in descending order ( ORDER BY Salary DESC ). 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 Filtering The outer query filters rows where Rank = N . Why It's Inefficient for Large Datasets? 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. 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. Memory Pressure Sorting and ranking large datasets consume significant memory. May spill to disk if data exceeds available RAM. 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. Redundant Work If multiple users request different N values, the entire ranking process repeats each time. When to Use This Approach? Small datasets ( 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.

Tuhin Paul · Answer

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 Why is this better? It eliminates sorting overhead . Works well with indexed Salary columns . Avoids full table scans for large OFFSET values .

Shubham Sidnale · Answer

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 .