Find max and second max salary for a employee table MySQL

Question

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

Answer 1

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

Answer 2

Try like this

SELECT (select max(Salary) from Employee) as MAXinmum),(max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary)) FROM Employee);

(Or)

Try this, n would be the nth item you would want to return

 SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC LIMIT n,1

In your case

 SELECT DISTINCT(column_name) FROM table_name ORDER BY column_name DESC limit 2,1;

Answer 3

Simplest way to fetch second max salary & nth salary

select 
 DISTINCT(salary) 
from employee 
 order by salary desc 
limit 1,1

Note:

limit 0,1  - Top max salary

limit 1,1  - Second max salary

limit 2,1  - Third max salary

limit 3,1  - Fourth max salary