Problem

Table: Employee

1
2
3
4
5
6

+-------------+------+
| 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:

1
2
3
4
5
6
7
8

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2

Output:

1
2
3
4
5

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Solution

Method 1 - Using Simple Order by

Code

1
2
3
4
5
6
7
8

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
1
2


##### Pandas
1
2
3
4
5
6
7

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

1
2
3
4
5
6
7
8
9

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:

1
2
3
4
5
6
7
8
9

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

1
2
3
4
5
6
7
8
9

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