How to find second highest salary in mysql

Question

How to find second highest salary in mysql. All record find in second highest salary.

Table : Employee
ID    salary      emp_name
1     400         A
2     800         B
3     300         C
4     400         D
4     400         C

*** Mysql Query: ***
SELECT * FROM employee ORDER by salary DESC LIMIT 1,2

This return two record.I do not know how many record in second highest salary.

Answer 1

SELECT sal
FROM emp
ORDER BY sal DESC
LIMIT 1, 1;

You will get only the second max salary.

And if you need any 3rd or 4th or Nth value you can increase the first value followed by LIMIT (n-1) ie. for 4th salary: LIMIT 3, 1;

Answer 2

Try this:

SELECT emp_name,salary 
FROM Employee
WHERE salary = (SELECT DISTINCT salary FROM Employee as emp1
                WHERE (SELECT COUNT(DISTINCT salary)=2 FROM Employee as emp2
                WHERE emp1.salary <= emp2.salary)) 
ORDER BY emp_name