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:
Sql
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:
Sql
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:
Sql
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:
Sql
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.
