Problem
Table: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key column for this table. Each row of this table contains information about the salary of an employee.
Write an SQL query to report the nth
highest salary from the Employee
table. If there is no nth
highest salary, the query should report null
.
The query result format is in the following example.
Examples
Example 1:
Input: Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
Output:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
Solution
Method 1 - Using Simple Order by
Code
SQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT M
);
END
But As we mentioned in previous post too that this solution won’t return correct value if there are two records for Nth Highest Salary.
Pandas
def nth_highest_salary(employee: pd.DataFrame, n: int) -> pd.DataFrame:
return employee.sort_values(
by='salary',
ascending=False,
).drop_duplicates(
subset=['salary'],
).iloc[n - 1:n][['salary']]
Method 2 - Using Dense Rank
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
with cte as
(select id, salary, dense_rank() over(order by salary desc) as rank from Employee)
select ifnull((select distinct salary from cte where rank=N),null)
);
END
Method 3 - Self Join
Code:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary
FROM Employee e1
WHERE N-1 = (SELECT COUNT(DISTINCT Salary) FROM Employee e2 WHERE e1.Salary < e2.Salary)
);
END
Method 4 - Using Offset
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET M
);
END