Production Postgres query returning different values than local sqlite query

Question

I have a query based on the answer to this question: http://stackoverflow.com/questions/2939061/handling-ties-when-ranking-from-the-highest-to-the-lowest/2939079#2939079

Given this dataset:

Name | Score
Mike | 5
John | 3
Mary | 3
Matt | 0

The following query returns a user array containing the correct values.

User.find_by_sql("SELECT id, score, (SELECT COUNT(DISTINCT outertable.score) + 1
FROM users WHERE score > outertable.score ORDER BY score ASC) AS rank 
FROM users as outertable GROUP BY outertable.id, outertable.score
ORDER BY score DESC LIMIT 30")

Name | Score | Rank
Mike | 5 | 1
John | 3 | 2
Mary | 3 | 2
Matt | 0 | 3

Running the exact same query in Postgres on Heroku, I receive this error:

ActiveRecord::StatementInvalid: PGError: ERROR:  more than one row returned by a subquery used as an expression

Adding a LIMIT 1 on the inner select results in the following funky data:

Name | Score | Rank
Mike | 5 | nil
John | 3 | 2
Mary | 3 | 2
Matt | 0 | 2

Thanks for the help!

Answer 1

There is some problem in your SQL.Change it to this:

SELECT  name,score, 
    (SELECT COUNT(DISTINCT score) + 1
     FROM users WHERE score > outertable.score 
     ) AS rank 
FROM users as outertable 
GROUP BY outertable.name, outertable.score
ORDER BY score DESC LIMIT 30

I have tried it without no problem!You could also try this statement.They have the same result.

select a.name,
       a.score,
       count(distinct b.score)+1 as rank
from 
    users a left join users b
    on a.score > b.score
group by
    a.name,a.score
order by a.score desc limit 30