I’m asking for help on an assessment question I recently got wrong, I’ve tried a number of solutions and think I kind of know what I’m trying to do, but can’t seem to figure out the syntax.
I have a table that looks like the below but with more records.
MyTable ID Name DivisionID ManagerID Salary 123 John Smith 100 789 40000 456 Harold Johnson 101 null 60000 789 Vicky Brown 100 null 80000
and have to select the row of the person with the 3rd highest salary, which I had no problem with. However, I also need to return, instead of ManagerID, the Manager Name, which needs to be looked up from the same table.
I’ve tried the following solution which seems to be a bit inelegant and has to have the same query hard-coded within it, so not ideal for scaling or general use:
SELECT table.ID, Name, DivisionID, (SELECT Name FROM table WHERE id=( SELECT ManagerID FROM table ORDER BY Salary DESC LIMIT 2,1) ) AS ManagerName, Salary FROM table ORDER BY Salary DESC LIMIT 2,1;
I think there may be some way of doing this with subqueries, e.g. first selecting a separate table within the query of just manager id and name, and then selecting from this – but I just can’t seem to get the syntax right or get my head around it. I think it might also be possible with table aliases where I select two different results from the same table under different aliases and then join the two, but again just can’t figure out how to do this. Below is what I’ve tried to do with aliases
SELECT a.ID, a.Name, a.DivisionID, b.Name AS ManagerName a.Salary FROM table a INNER JOIN table b ON a.ManagerID=b.ID ORDER BY Salary DESC LIMIT 2,1;
First of all, when asked to return the nth greatest/least value, you must ask back what to do in case of ties. They want the person with third highest salary, so with salaries 1000, 1000, 900, 900, 800, 800, 700, 600, 500, I’d suppose you want to return the persons that earn 800, because that is the third highest salary. If you just order the persons by salary, skip two and take the third, then you pick one of the persons with a salary of 900 arbitrarily, and 900 is not even the third highest, but the second highest salary.
In order to get the manager, simply join the table again. You should use an outer join for the case that an employee with the third highest salary is a manager themselves.
The straight-forward solution is to rank the rows with
select * from ( select t.*, dense_rank() over (order by salary desc) as rnk from mytable t ) employee left join mytable manager on manager.id = employee.managerid where employee.rnk = 3;
DENSE_RANK since version 8. In older versions you must look up the same table again. Select the distinct salaries and use your limit/offset clause on those saleries.
select * from mytable employee left join mytable manager on manager.id = employee.managerid where employee.salary = ( select distinct salary from mytable order by salary desc limit 2, 1 );
Answered By – Thorsten Kettner
Answer Checked By – Cary Denson (BugsFixing Admin)