Our problem is to find 2nd highest salary from the employee table using mysql .
EMPLOYEE TABLE
Steve 150$
James 120$
Andrew 120$
Mark 100$
First Method: We will sort the salaries in descending order and will limit the solution by one.
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 1 , 1
But this will not give all the results if there are two employees whose salaries are same(second highest).
Above solution will return Steve only.
So here is the alternative way using nested subquery:
SELECT name, salary FROM EMPLOYEE WHERE salary = (SELECT MAX(salary) FROM EMPLOYEE WHERE salary < (SELECT MAX(salary) FROM EMPLOYEE))
Pingback: Find second Highest Salary for each department | CrazyforCode
SELECT name, max(salary)
FROM EMPLOYEE
WHERE salary < (SELECT MAX(salary) FROM EMPLOYEE)
Better approach ..Using only one sub query..
Sumit
your Quiry gives wrong answer..
# SQL Query for the 2nd height salary holder:
SELECT name, salary
FROM EMPLOYEE
WHERE salary < (SELECT MAX(salary) FROM EMPLOYEE) ORDER BY salary DESC LIMIT 1
student table with id, student_name, department_id, salary
select department_id, min(salary) as Second_Highest_Per_Department, student_name from ( select department_id,student_name, salary from student where salary NOT IN ( select max( salary ) from student group by department_id ) ) as tab group by department_id