The Tech Platform
Apr 14, 20212 min
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.
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 M, 1
);
END
This is another solution to find Nth highest salary problem, this was not accepted by LeetCode compiler but they work fine on Database
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# WRITE your MySQL query statement below.
SELECT Salary FROM Employee a
WHERE N = (SELECT COUNT(Salary) FROM Employee b WHERE
a.Salary <= b.Salary));
END
If you want to test in your local database then you can use the following SQL query to create an Employee table and populate with some sample data. After that, you can run the SQL query to find the Nth highest salary for testing.
CREATE TABLE Employee (
Id INT NOT NULL,
Salary INT NULL);
INSERT INTO Employee VALUES (1, 100);
INSERT INTO Employee VALUES (2, 200);
INSERT INTO Employee VALUES (3, 300);
Here is the SQL query you can use to find the Nth highest salary for the above table, you can run this in your local database and it should return the
SELECT Salary FROM Employee a
WHERE N = ( SELECT COUNT(Salary) FROM Employee b WHERE a.Salary <= b.Salary );
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. You can see that we have used the above query to find the highest, second-highest, and third-highest salary from the employee table.