Nth Highest Salary — Day 82(SQL)

Photo by 金 运 on Unsplash

Today’s question is a SQL question. Let us look into the problem statement.

177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

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

A few days back, we found the solution to finding the second highest salary question. Let us recall the code snippet.

SELECT IFNULL((SELECT DISTINCT Salary FROM EMPLOYEE
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), NULL) AS SecondHighestSalary;

Can we try using the same code with minor modifications to solve the given problem statement?

What are the modifications that need to be made here? Since we need to find the “N” highest; we need to include “N” in our code.

Let us see the code snippet.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM EMPLOYEE
ORDER BY Salary DESC
LIMIT 1 OFFSET N);
END