sql - Trying to find third lowest salary in mysql

Question

I am using a nested SQL query for finding the third lowest salary but the query doesn't show the correct name of the employee instead it shows gives the highest-paid employee name with the third-lowest salary. This is my query.

SELECT first_name, MIN(salary)
  FROM employee
 WHERE salary > (SELECT min(salary) 
                 FROM employee
                 WHERE salary > (SELECT min(salary)
                 FROM employee)
                ); 

question from:https://stackoverflow.com/questions/65928719/trying-to-find-third-lowest-salary-in-mysql

Answer 1

You can use OFFSET/LIMIT to get the third lowest:

SELECT first_name, salary
  FROM employee
 WHERE salary
ORDER BY salary
LIMIT 1 OFFSET 2

---

The reason why your original query didn't work, is firstly, your select is:

SELECT first_name, MIN(salary)

which means that there is an implicit "group everything" here, and MySQL interprets the first_name as ANY(first_name).

Furthermore, it's extremely inefficient to do it that way.