current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
Obviously, I'm not trying to just fetch the top 20 rows of users. I've simplified my query to more clearly illustrate the syntax error –  Matm Feb 23 '13 at 1:11

2 Answers 2

up vote 1 down vote accepted

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 at the next query level.

The manual tells us here:

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.

share|improve this answer
up vote 0 down vote

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.