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

Why can't I use only one SELECT clause with RANK() and WHERE condition – Postgresql

I can get the rank alias with this query:

SELECT *, 
  RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table

Result:

some_field value rk
same 10 1
same 20 2
same 30 3

And I tried to query with:

SELECT *, 
  RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table
WHERE rk = 1

I got this error message
column "rk" does not exist

If I tried a subquery, it works :

SELECT *
FROM (
  SELECT *,
    RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
  FROM my_table
) AS t
WHERE rk = 1

Result:

some_field value rk
same 10 1

But my question is why can’t we use just one SELECT to do so.

Is it because I use a function in my query?

Tags:

2

Answers


  1. 0

    Yes.. You can’t use the column name in where clause. First understand, SQL execution flow. As per your sql statment, it will execute in below order.

    1. From clause.
    2. Apply filter where clause
    3. Execute select statement.

    So in your sql statment, rk column is generating at step 3. But you are accessing at step 2 which is not possible. Hope it will clear

    Login or Signup to reply.
  2. 0

    You can use the rank expression as a constraint… like so.

    SELECT *, 
 1 AS rk
FROM my_table
WHERE  RANK() OVER (PARTITION BY some_field ORDER BY value) = 1

    Hope it helps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search