How to Find the nth Highest Salary in SQL: A Step-by-Step Guide

Want to ace your next SQL interview? One common question you’re likely to encounter is how to find the employee with the nth highest salary. Whether it’s the highest, second-highest, or even the fifth-highest, knowing how to query this efficiently is key for demonstrating your SQL skills. Let’s walk through three methods to solve this with examples.

Understanding the Problem

Imagine you have an employee table containing details like employee_id, name, email, and salary. The goal is to write a query that finds the employee with the nth highest salary — this could be the second, third, or even fifth highest.

We’ll cover three approaches:

1. Using the MAX() function.
2. Using the TOP function.
3. Using an analytical function like DENSE_RANK().

Ready? Let’s dive into the details for each method.

Method 1: Using the MAX() Function

The MAX() function helps us find the maximum salary in a table, but we can tweak it to find the nth highest salary.

Steps to Follow

1. Start with the highest salary:

   SELECT MAX(salary) 
   FROM employee;
   

   This gives the single highest salary.

2. To find additional salaries (like the second-highest), compare salaries lower than the maximum:

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

   This query finds the second-highest salary by excluding the maximum salary.

3. To find the nth highest, keep nesting subqueries:

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

Though effective, this method becomes cumbersome for higher "n" values due to repeated subqueries.

Bonus: Fetch Full Employee Details

To pull all employee details with the nth highest salary, use this query as a subquery in the WHERE clause:

SELECT * 
FROM employee 
WHERE salary = (SELECT MAX(salary) 
                FROM employee 
                WHERE salary < (SELECT MAX(salary) FROM employee));

This approach works but can be tedious for complex needs.

Method 2: Using the TOP Function

The TOP function is another tool to extract top rows from your dataset. Paired with sorting, it lets you fetch salaries in ranked order.

Steps to Follow

1. Fetch the top salary:

   SELECT TOP 1 * 
   FROM employee 
   ORDER BY salary DESC;
   

   The ORDER BY clause ensures the highest salary is selected.

2. Fetch the nth highest salary by increasing the TOP number:

   SELECT TOP 3 * 
   FROM employee 
   ORDER BY salary DESC;
   

   Replace 3 with any number to fetch top "n" records.

3. To get exactly the nth salary, wrap it in a subquery:

   SELECT TOP 1 * 
   FROM (SELECT TOP 3 * 
         FROM employee 
         ORDER BY salary DESC) AS temp 
   ORDER BY salary ASC;
   

Why It Works

The inner query sorts the first three records in descending order. The outer query re-sorts them in ascending order and returns the topmost result, which is the nth salary.

This approach is simpler than using MAX() and works well for most scenarios.

Method 3: Using DENSE_RANK()

The best way to find rankings in SQL is by using analytical functions like DENSE_RANK(), ROW_NUMBER(), or RANK(). These functions assign ranks to records based on the specified order. For this task, DENSE_RANK() is ideal because it handles duplicate salaries neatly.

Steps to Follow

1. Assign ranks to employee records:

   SELECT *, 
          DENSE_RANK() OVER (ORDER BY salary DESC) AS ranking 
   FROM employee;
   

   This generates a ranking column that assigns a rank to each row based on the salary.

2. Find the nth rank:

   SELECT * 
   FROM (SELECT *, 
                DENSE_RANK() OVER (ORDER BY salary DESC) AS ranking 
         FROM employee) AS ranked_data 
   WHERE ranking = 3;
   

   Replace 3 with any rank to find the nth highest salary.

Why Use DENSE_RANK()?

If two employees have identical salaries, they’ll share the same rank. This ensures no gaps in the rankings, making it more consistent than ROW_NUMBER() or RANK() when duplicates exist.

When Should You Use Each Method?

• MAX(): Best for quick queries to find the top couple salaries.
• TOP: Great for fetching ranked records when "n" changes often.
• DENSE_RANK(): Ideal for more complex scenarios or when duplicate salaries exist.

Conclusion

Whether you’re preparing for an interview or solving real-life data problems, knowing how to find the nth highest salary efficiently is a must-have skill. The method you choose depends on your needs — MAX() for simple queries, TOP for flexibility, or DENSE_RANK() for advanced scenarios.

Got an upcoming interview? Practice these queries and variations to sharpen your skills. The more you work with them, the more confident you’ll be. Happy querying!

Learn More

For any questions, reach out to learn@knowstar.org.

Consider expanding your expertise with these certifications:

• Google Data Analytics Professional Certificate: https://imp.i384100.net/OR37oQ
• Google Advanced Data Analytics Professional Certificate: https://imp.i384100.net/eK1WmQ

• Best SQL and Data Analytics Books

• T-SQL Fundamentals (By Itzik Ben-Gan) - https://amzn.to/4koKGdC
• Ace the Data Science Interview - https://amzn.to/3D2ne5n