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 table.

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

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

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

Software Engineer. Find me @ www.linkedin.com/in/annamariya-jt

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store