Using ROW_NUMBER() Function in a WHERE statement in Postgres

Question

The following works in Postgres:

SELECT
    *
FROM
    (
        SELECT
            product_id,
            product_name,
            price,
            ROW_NUMBER () OVER (ORDER BY product_name)
        FROM
            products
    ) x
WHERE
    ROW_NUMBER BETWEEN 6 AND 10;

My question is why does the following simpler attempt not work?

        SELECT
            product_id,
            product_name,
            price,
            ROW_NUMBER () OVER (ORDER BY product_name)
        FROM
            products
     
WHERE
    ROW_NUMBER BETWEEN 6 AND 10;

sticky bit · Accepted Answer · 2021-03-06 16:36:02Z

2

Because window functions, like row_number(), are applied to the result of the query (in your case that's the inner one), i.e. after the WHERE clause (not statement) has already taken effect.

answered Mar 6, 2021 at 16:36

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Ginsburg Over a year ago

So the variable does not exist in the namespace and cannot be referred to unless it is returned by an inner query. Interesting.

Collectives™ on Stack Overflow

Using ROW_NUMBER() Function in a WHERE statement in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related