Assume you are given the following straightforward database table, Employee, which has the columns Employee ID and Salary:
Employee ID Salary
I wish to write a query select max(salary) as max_salary, 2nd_max_salary from employee.
Then it should return:
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)
I am not able to how to join these 2 results for the desired result. Can someone please help me with this?