Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas)
Question
Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas)
Solution
Sure, here is a solution using SQL:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee)
This query works by first finding the maximum salary in the Employee table. It then finds the maximum salary that is less than this value, which is the second highest salary.
If you want to do the same in Python using pandas, you can use the following code:
def second_highest_salary(df):
sorted_salaries = df['Salary'].sort_values(ascending=False).unique()
if len(sorted_salaries) < 2:
return None
else:
return sorted_salaries[1]
This function sorts the 'Salary' column in descending order and removes duplicates. If there is no second highest salary (i.e., all employees have the same salary), it returns None. Otherwise, it returns the second highest salary.
Similar Questions
Which SQL function is used to find the highest value in a column?OptionsMAX()AVG()MIN()SUM()
Select the correct answerWhich SQL function is used to find the highest value in a column?OptionsAVG()MIN()SUM()MAX()
Which SQL function is used to find the highest value in a column?OptionsMIN()SUM()MAX()AVG()
Which of the below queries displays employees' name and new salary after the increment of 1000
Based on a salary of $2500, what is the employee's contribution for a Singapore employee to his or her CPF accounts?$600$500$700$300
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.