Question 114250 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – strange WHERE clause in sql query ; WHERE (select …) = 3

Usually with WHERE clause in SQL queries, in WHERE we check from the table if a condition is met, but with the following code:

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

This following sub-query Sub is returning a number.

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

but I don’t understand how we check WHERE Sub = 3, it doesn’t make sense to me. Can you explain?

The entire query finds the 4th highest salary of teacher.

Tags:

2

Answers


  1. 0

    You can use a subquery anywhere where you can use an expression, so why not on the left side of the equality operator?

    But this way to solve the problem is rather ugly. Why not

    SELECT name
FROM (SELECT DISTINCT ON (salary)
             name, salary
      FROM teacher) AS t
ORDER BY salary DESC
OFFSET 3 FETCH FIRST 1 ROWS ONLY;
    Login or Signup to reply.
  2. 0

    Perhaps more comprehensive with this query :

    WITH T AS
(
SELECT name, COUNT(*) OVER(ORDER BY salary) AS N
FROM teacher as T1
)
SELECT name
FROM   T 
WHERE  N > 3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search