Can someone explain this SQL statement? Has to do with count function

user_19383578 0
Viewed 32
SELECT*
FROM employee A
WHERE n-1 = (SELECT count (*)
                FROM employee B
                WHERE B.salary > A.salary

So I'm trying to get the nth highest salary from the employee table. This code works exactly as I want it to, but I don't understand it particularly the 3rd line where "WHERE n-1 = (SELECT count(*)"

I understand how the count function works, but I don't get what happens when you input a number and state WHERE it equals to the count function

mysql
1 Answers

It looks like an example from a tutorial or book. Where it says n-1, you would substitute an integer value.

For example, if you want the 4th highest salary, you'd substitute 4-1, or 3.

So the query would be:

SELECT*
FROM employee A
WHERE 3 = (SELECT count (*)
                FROM employee B
                WHERE B.salary > A.salary);

The subquery that returns the count is a correlated subquery. So it searches for rows with a greater salary relative to A.salary, the salary of the respective row currently being evaluated in the outer query. This means it will run the subquery many times, once for each row of the outer query. That's usually what a correlated subquery does. It has to do that, because the result of the subquery may be different for each row of the outer query.

So this subquery will return the count of employees whose salary is greater than the salary of the respective row in the outer query. If that count is 3, then there are exactly three employees with a greater salary than the employee represented by the row A. Therefore that employee has the 4th highest salary.

In MySQL 8.0, you can use window functions, so another way to get this result without using a correlated subquery is the following:

SELECT *
FROM (
    SELECT *, RANK() OVER (ORDER BY salary DESC) AS `rank`
    FROM employee
) AS t
WHERE `rank` = 4;
Bill_Karwin_208600
Related