bestrahul21 bestrahul21 - 7 months ago 18
SQL Question

How to get second highest salaried employees in mysql

I have an employee table in MySQL with below entries. I need to find all the employees having second highest salaries. In this case, it would be c and d.

id | name | salary
1 | a | 1000
2 | b | 1000
3 | c | 500
4 | d | 500
5 | e | 400


I tried running below query

SELECT name, MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) from employee);


But this query returns just c as a result. How to get both c and d in result?
I looked at bunch of similar questions posted but none of them mentioned how to get multiple rows for second highest salary.

Answer

You can get it by this:

2nd Largest Salary:

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))

3rd Largest Salary:

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)))


Helpful links:
http://www.mysqltutorial.org/select-nth-highest-record-database-table-using-mysql.aspx http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/ http://www.coderanch.com/t/530503/JDBC/databases/select-Nth-highest-salary-table