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)
2
Answers
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.Focusing on the subquery:
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: