Production Postgres query returning different values than local sqlite query

Question

I have a query based on the answer to this question: Handling ties when ranking from the highest to the lowest

Given this dataset:

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!

tinychen · Accepted Answer · 2010-08-03 02:52:09Z

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

answered Aug 3, 2010 at 2:52

tinychen

2,0992 gold badges13 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Production Postgres query returning different values than local sqlite query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related