Apr 14, 20212 min read

Nth Highest Salary Example in MySQL and SQL Server

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.

1. Accepted Solution

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

2. Alternate Solution

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 &lt;= b.Salary));   
    END

3. How to create own Employee table for testing?

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);

4. SQL query to find the Nth highest salary

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 &lt;= 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.