Find max and second max salary for a employee table MySQL
mysql, sql
Solution
You can just run 2 queries as inner queries to return 2 columns:
select
(SELECT MAX(Salary) FROM Employee) maxsalary,
(SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )) as [2nd_max_salary]
SQL Fiddle Demo
Problem
Suppose that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary: ``` Employee Employee ID Salary 3 200 4 800 7 450 ``` I wish to write a query select max(salary) as max_salary, 2nd_max_salary from employee then it should return ``` max_salary 2nd_max_salary 800 450 ``` i know how to find 2nd highest salary ``` SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee ) ``` or to find the nth ``` SELECT FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary) ``` but i am unable to figureout how to join these 2 results for the desired result