Postgres Rank As Column

Question

I have the following query:

SELECT name, rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position FROM items

And I'd now like to do a where clause on the rank() function:

SELECT name, rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position FROM items WHERE position = 1

That is, I want to query the most loved item for each user. However, this results in:

PGError: ERROR: column "position" does not exist

Also, I'm using Rails AREL to do this and would like to enable chaining. This is the Ruby code that creates the query:

Item.select("name, rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position").where("position = 1")

Any ideas?

Answer 1

You need to "wrap" it into a derived table:

SELECT * 
FROM (
    SELECT name, 
           rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position 
    FROM items
) t
WHERE position = 1

Answer 2

My first thought was, "Use a common table expression", like this untested one.

WITH badly_named_cte AS (
  SELECT name, 
         rank() OVER (PARTITION BY user_id 
                      ORDER BY love_count DESC) AS position 
  FROM items
)
SELECT * FROM badly_named_cte WHERE position = 1;

The problem you're seeing has to do with the logical order of evaluation required by SQL standards. SQL has to act as if column aliases (arguments to the AS operator) don't exist until after the WHERE clause is evaluated.