CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Matm
Matm

Reputation: 583

Postgres Window Function Syntax

Why does the following query:

select ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY time DESC) as rownum FROM users where rownum < 20;

produce the following error?

ERROR: column "rownum" does not exist LINE 1: ...d ORDER BY time DESC) as rownum FROM users where rownum < 2...

How can I structure this query so that I get the first 20 items, as defined by my window function?

user_id and time are both defined columns on users.

Upvotes: 2

Views: 2459

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658482

It would work like this:

SELECT *
FROM  (
   SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY time DESC) AS rownum
   FROM   users
   ) x
WHERE  rownum < 20;

The point here is the sequence of events. Window functions are applied after the WHERE clause. Therefore rownum is not visible, yet. You have to put it into a subquery or CTE and apply the WHERE clause on rownum in the next query level.

Per documentation:

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses. Also, window functions execute after regular aggregate functions. This means it is valid to include an aggregate function call in the arguments of a window function, but not vice versa.

Upvotes: 6

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

Because the where clause executes before the select so it does not know about that alias yet. Do it like this:

select *
from (
    select ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY time DESC) as rownum 
    FROM users 
) s
where rownum < 20;

Upvotes: 1

Related Questions