Nth Highest Salary SQL Challenge - MySQL Solution

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

Output for n = 2 with only one employee:

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.