CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Kevin Sylvestre
Kevin Sylvestre

Reputation: 38052

Postgres Rank As Column

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?

Upvotes: 5

Views: 2064

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

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.

Upvotes: 5

user330315
user330315

Reputation:

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

Upvotes: 6

Related Questions