can you explain the logic of this query-CodePudding

I have this query that finds the name of the teacher with the 4-th highest salary. I don't understand this part

SELECT COUNT (DISTINCT T2.salary)
FROM teacher as T2
WHERE T2.salary > T1.salary
) = 3

from

SELECT name
FROM teacher as T1
WHERE (
SELECT COUNT (DISTINCT T2.salary)
FROM teacher as T2
WHERE T2.salary > T1.salary
) = 3;

The way I understand count is that it gives a final result, not that we can interrupt its work by specifying a number.

This is the teacher table: https://imgur.com/a/tZVk1O8 (I couldn't upload it here due to a server error)

CodePudding user response：

I wouldn't write it that way, but what it does is count, for each teacher, how many salaries are higher than his/her salary.

If 3 salaries are higher than a given teacher's salary then that teacher must be ranked 4th.

The performance of this query will be disastrous with large tables. You should use the rank window function instead.

CodePudding user response：

Focusing on the subquery:

SELECT COUNT(DISTINCT T2.salary)
FROM teacher AS T2
WHERE T2.salary > T1.salary

This will return the count of distinct teachers having a salary greater than the teacher, in each row of the teacher table. Asserting that this count be equal to 3 means that any matching teacher would have the 4th highest salary (since first 3 positions excluded).

Note that your logic should behave identically to DENSE_RANK. You could also have used:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) rnk
    FROM teacher
)

SELECT name
FROM cte
WHERE rnk = 4;