Similar Problems

Similar Problems not available

Nth Highest Salary - Leetcode Solution

Companies:

LeetCode:  Nth Highest Salary Leetcode Solution

Difficulty: Medium

Topics: database  

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

Table: Employee

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

Solution:

We can solve this problem by using the LIMIT clause and ORDER BY clause together.

First, we need to sort the salary column in descending order using the ORDER BY clause. Then, we can use the LIMIT clause to get the nth highest salary. For example, if we want to get the 2nd highest salary, we can set the LIMIT to 1 (because there is already a row with the highest salary).

Here is the SQL query to get the nth highest salary:

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT n-1, 1;

Explanation:

  • The DISTINCT keyword ensures that we only get distinct salary values.
  • The ORDER BY clause sorts the salary column in descending order.
  • The LIMIT clause is used to get the nth highest salary. We set the offset to n-1 (because we want to skip the first n-1 rows) and the count to 1 (because we only want to get one row).

Example:

Let's say we have the following Employee table:

| Id | Salary | |----|--------| | 1 | 1000 | | 2 | 2000 | | 3 | 3000 | | 4 | 4000 | | 5 | 5000 |

If we want to get the 3rd highest salary, we can plug in n=3 in the query. So the query becomes:

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 2, 1;

This will return the following result:

| Salary | |--------| | 3000 |

Explanation:

  • The ORDER BY clause sorts the salary column in descending order, so the highest salary is 5000 and the lowest salary is 1000.
  • The LIMIT clause with offset=2 and count=1 skips the first two highest salaries, and returns the 3rd highest salary which is 3000.

If we want to get the 6th highest salary, there is no such record in the table. In that case, the query will return NULL.

Conclusion:

The Nth Highest Salary problem can be solved by using the LIMIT clause and ORDER BY clause together in a SQL query. We sort the salary column in descending order and use the LIMIT clause to get the nth highest salary. If there is no nth highest salary, we get NULL.

Nth Highest Salary Solution Code

1