Problem Statement
In this post, we'll be addressing a common SQL problem: finding the nth highest salary from a table of employees. If there is no nth highest salary, the query should return null. This type of problem often shows up in coding assessments and job interviews, so let's delve into the solution. The structure of the Employee
table is as follows:
Column Name | Type |
---|---|
id | int |
salary | int |
The id
is the primary key, and each row contains salary information for an employee.
The objective is to write an SQL query that can return the nth highest salary.
Example 1:
id | salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
With n = 2, the output would be:
getNthHighestSalary(2) |
---|
200 |
Example 2:
id | salary |
---|---|
1 | 100 |
With n = 2, the output would be:
getNthHighestSalary(2) |
---|
null |
Approaches to the Solution
We'll explore two approaches that involve creating a SQL function to solve the problem. Each method uses different concepts and techniques in SQL. We'll explain each one and highlight their strengths and weaknesses.
1. Using a Stored Function with DENSE_RANK()
The first solution creates a stored function getNthHighestSalary
which uses the DENSE_RANK()
window function to rank the salaries in descending order.
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
SELECT s.salary
FROM
(
SELECT DISTINCT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) [drank]
FROM Employee
) [s]
WHERE s.drank = @N
);
END
This approach provides a clear and logical solution. However, it is not the most performant, with a runtime of 816ms, beating only 29.26% of LeetCode submissions.
2. Using a Stored Function with ROW_NUMBER()
The second solution also creates a stored function, but instead uses the ROW_NUMBER()
window function to rank the salaries in descending order.
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
RETURN (
SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM (
SELECT DISTINCT salary
FROM Employee
) AS temp
) AS ranked_salaries
WHERE rn = @N
);
END
This approach performs slightly better, with a runtime of 704ms, beating 55.52% of LeetCode submissions.
Conclusion
These solutions demonstrate how to use stored functions and window functions to solve a common problem in SQL. While the two approaches are similar, using ROW_NUMBER()
slightly outperforms DENSE_RANK()
on LeetCode.
However, the best solution can vary depending on the RDBMS and the specific dataset. Therefore, it's always a good idea to test different methods in your specific environment.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
Top comments (2)
Intresting post
Thanks you could check my other post in this series