CODEWITHSUNDEEP
sqlpostgresql
v_head
v_head

Reputation: 805

can you explain the logic of this query

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://i.sstatic.net/arm8d.jpg (I couldn't upload it here due to a server error)

Upvotes: 2

Views: 86

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520988

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;

Upvotes: 1

Jonathan Jacobson
Jonathan Jacobson

Reputation: 1518

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.

Upvotes: 0

Related Questions