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.
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.
Window functions are permitted only in the
SELECTlist and theORDER BYclause of the query. They are forbidden elsewhere, such as inGROUP BY,HAVINGandWHEREclauses. 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.
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;
users. I've simplified my query to more clearly illustrate the syntax error