Description
Table: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.
Write a solution to find the nth highest salary from the Employee table. If there is no nth highest salary, return null.
The result format is in the following example.
Examples:
Example 1:
Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
Output:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
Example 2:
Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
n = 2
Output:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null |
+------------------------+Solution in Pandas
Python
import pandas as pd
def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
# If N is less than or equal to 0, it's invalid, so return null
if N <= 0:
return pd.DataFrame({'getNthHighestSalary({})'.format(N): [None]})
# Drop duplicate salaries to only consider unique salary values
unique_salaries = employee['salary'].drop_duplicates()
# Sort the unique salaries in descending order
sorted_salaries = unique_salaries.sort_values(ascending=False)
# Check if N is within the range of available salaries
if N > len(sorted_salaries):
# If N is greater than the number of unique salaries, return null
return pd.DataFrame({'getNthHighestSalary({})'.format(N): [None]})
else:
# Otherwise, return the Nth highest salary
nth_salary = sorted_salaries.iloc[N-1]
return pd.DataFrame({'getNthHighestSalary({})'.format(N): [nth_salary]})Explanation:
- Remove Duplicate Salaries: The code first drops any duplicate salary values from the
salarycolumn usingdrop_duplicates()to ensure only unique salaries are considered. - Sort Salaries: It then sorts the unique salaries in descending order using
sort_values(ascending=False). This will allow easy access to the nth highest salary. - Check for Valid N: Before accessing the nth salary, the code checks whether
Nis greater than the number of unique salaries. IfNis out of range, it returns a DataFrame containingNone. - Return the Nth Highest Salary: If
Nis valid, the code retrieves the nth highest salary usingiloc[N-1]and returns it in a DataFrame.
Solution in MySQL
SQL
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
-- Declare a variable M to hold the value of N-1
DECLARE M INT;
-- Set the value of M to N-1 because the LIMIT clause is zero-based
SET M = N - 1;
-- Return the nth highest salary using a subquery
RETURN (
SELECT (
-- Subquery to select the nth highest distinct salary
SELECT DISTINCT salary
FROM Employee
-- Order salaries in descending order to get the highest salaries first
ORDER BY salary DESC
-- Use LIMIT to skip the first M (N-1) salaries and then return the next one
LIMIT M, 1
) AS nth_highest_salary
);
END;