Second highest Salary

[QuXiangjie]

How to find the second highest Salary? Question SQL solution

SELECT (
    SELECT DISTINCT Salary
      FROM Employee
     ORDER BY Salary DESC
     LIMIT 1
    OFFSET 1
) AS SecondHighestSalary;

The LIMIT row_count determines the number of rows (row_count) returned by the query. The OFFSET offset clause skips the offset rows before beginning to return the rows.

Pandas solution

import pandas as pd

def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:
    sorted_salary=employee['salary'].sort_values(
        ascending=False
    ).drop_duplicates()
    return pd.DataFrame({
        'SecondHighestSalary':[None if sorted_salary.size<2 else sorted_salary.iloc[1]]
    })

Good solution

[QuXiangjie]

The question above can be upgraded to a function to choose Nth highest salary.

SQL solution

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
 select ifnull((
 select distinct salary 
 from employee
 order by salary desc
 limit 1
 offset N),null) 
);
END

The IFNULL function takes two arguments. It checks to see if the first argument is not NULL. If it is not NULL desc 降序 asc 升序 distinct drop the duplicate

Pandas solution

import pandas as pd

def nth_highest_salary(employee: pd.DataFrame, N: int) -> pd.DataFrame:
    sorted_salaries=employee['salary'].sort_values(
        ascending=False
    ).drop_duplicates()

    # If N exceeds the number of unique salaries or N is less than or equal to 0, return None
    if N > len(sorted_salaries) or N <= 0:
        return pd.DataFrame({f'getNthHighestSalary({N})': [None]})
    else:
        nth_highest = sorted_salaries.iloc[N - 1]
        return pd.DataFrame({f'getNthHighestSalary({N})': [nth_highest]})

don't forget to use employee['salary'].sort_values and also the False, F should be Uppercase