Solution For Nth Highest Salary

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:

sql
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:

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

Step by Step Implementation For Nth Highest Salary

/**
 * Returns the nth highest salary from the given array of salaries.
 * 
 * @param salaries array of salaries
 * @param n        nth highest salary to return
 * @return nth highest salary
 */
public static int nthHighestSalary(int[] salaries, int n) {
    // sort salaries in descending order
    Arrays.sort(salaries);

    // return nth element from end of array (nth highest salary)
    return salaries[salaries.length - n];
}

# Write your MySQL query statement below
SELECT DISTINCT Salary 
FROM Employee 
ORDER BY Salary DESC 
LIMIT 1 OFFSET n-1

// There are many ways to solve this problem. One approach would be to // use the built-in sort method to sort the salaries in descending order, // then return the element at the index specified by the nth parameter. function nthHighestSalary(salaries, n) { // sort salaries in descending order const sortedSalaries = salaries.sort((a, b) => b - a); // return the element at the nth index return sortedSalaries[n - 1]; }

#include  
#include  
#include  

using namespace std; 

class Solution { 
public: 

// Function to return the nth highest salary from the list of salaries 
// Note: The salaries should be in descending order 
int nthHighestSalary(vector& salaries, int n) 
{ 
	// Sort the salaries in descending order 
	sort(salaries.begin(), salaries.end(), greater()); 

	// Return the nth element from the sorted vector 
	return salaries[n - 1]; 
} 
};

using System; 

public class Solution { 

public int NthHighestSalary(int n) { 

// TODO: Implement your solution here 

} 

}