1. Introduction
In this blog post, we will delve into a common SQL query challenge: finding the nth highest salary from a table. We'll demonstrate this using the Employee table in a MySQL database.
Problem
Our objective is to write a MySQL query to determine the nth highest salary from the Employee table. If there is no nth highest salary (such as in cases where the number of employees is less than n), the query should return null.
Database Table:
Employee Table:id | salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
3. MySQL Solution
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT DISTINCT salary FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET N
);
END;
Output:
Output for n = 2:getNthHighestSalary(2) |
---|
200 |
getNthHighestSalary(2) |
---|
null |
Explanation:
1. The getNthHighestSalary function is created to find the nth highest salary.
2. We adjust N by subtracting 1 because the OFFSET clause in MySQL is zero-based.
3. The query selects distinct salaries, orders them in descending order, and applies LIMIT and OFFSET to retrieve the nth highest salary.
4. If the nth salary doesn't exist (e.g., n is greater than the number of distinct salaries), the query returns null.
Comments
Post a Comment