Nth Highest Salary

medium sorting database

Problem

Write a SQL function getNthHighestSalary(N) that returns the Nth highest distinct salary from Employee, or NULL when fewer than N distinct salaries exist.

SQL: SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET N-1;

Inputsalaries = [100, 200, 300], N = 2

Output200

salaries (comma-separated)

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (SELECT DISTINCT salary FROM Employee
          ORDER BY salary DESC LIMIT 1 OFFSET N-1);
END

Explanation

We want the Nth highest distinct salary. The query builds it directly: list the salaries, drop duplicates, sort them from high to low, then jump to the Nth one.

SELECT DISTINCT salary removes repeated pay values so that ties count as a single rank. ORDER BY salary DESC puts the biggest salary first.

The clever part is LIMIT 1 OFFSET N-1: skip the first N-1 rows and take just one. Row index N-1 is exactly the Nth highest because counting starts at zero.

Example: salaries [100, 200, 300] with N = 2. Sorted descending they are 300, 200, 100. We offset past 1 row (skip 300) and take one — giving 200.

If there are fewer than N distinct salaries, the offset lands past the end and the subquery returns NULL, which is exactly the required answer.

Time: O(n log n) Space: O(n)