How Do You Find the Nth Highest Salary in SQL?

A common question in technical interviews revolves around finding the Nth highest salary from a given set of employee salaries stored in a database. This is a vital skill for SQL developers as it tests understanding of SQL queries, aggregate functions, and subqueries.

To illustrate this task, let’s assume we have a table named employees with the following structure:

1CREATE TABLE employees (
2    id INT PRIMARY KEY,
3    name VARCHAR(100),
4    salary DECIMAL(10, 2)
5);

Suppose we want to find the 3rd highest salary among all employees in this table. There are various approaches to solve this problem, but we will cover a few efficient methods.

Method 1: Using a Subquery with DISTINCT

One straightforward way to identify the Nth highest salary is by using a subquery combined with the DISTINCT keyword. Here’s how to do it:

1SELECT DISTINCT salary
2FROM employees
3ORDER BY salary DESC
4LIMIT 1 OFFSET 2; -- 2 for finding the 3rd highest

In this query, ORDER BY salary DESC sorts the salaries in descending order. The LIMIT 1 OFFSET 2 clause retrieves the third record from this sorted list, which corresponds to the third highest salary.

Method 2: Using Common Table Expressions (CTE)

Another approach employs Common Table Expressions (CTEs), which can improve readability and organization of the query. Here is an example:

1WITH RankedSalaries AS (
2    SELECT DISTINCT salary,
3           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
4    FROM employees
5)
6SELECT salary
7FROM RankedSalaries
8WHERE rank = 3;

In this example, we first create a CTE called RankedSalaries, where we assign a unique rank to each distinct salary based on its order. The ROW_NUMBER() function provides sequential numbering for the sorted salaries. Finally, we select the salary with a rank of 3.

Method 3: Using a Self-Join

A less common method is to utilize a self-join. This approach might be less efficient but demonstrates another way to think about the problem. Here is how you can implement it:

1SELECT DISTINCT e1.salary
2FROM employees e1
3WHERE (
4    SELECT COUNT(DISTINCT e2.salary)
5    FROM employees e2
6    WHERE e2.salary > e1.salary
7) = 2; -- 2 for finding the 3rd highest

In this case, for every salary in the outer query (aliased as e1), we count how many distinct salaries in the inner query (aliased as e2) are greater than the outer salary. If exactly two salaries are greater, then the outer salary is the third highest.

Considerations

When determining the Nth highest salary, consider potential duplicates in the salary data. If your dataset might contain duplicate values, using DISTINCT is crucial to ensure you get unique salary ranks. Keep in mind that if there are fewer than N distinct salaries, your query may return no result, so you might want to handle such cases in your code or through error-checking logic.

This SQL challenge is a great way to showcase your reasoning and problem-solving skills in a technical interview setting.