How to approach GROUP BY in PostgreSQL using Rails?

Question

I have a Rails 3 app that keeps high scores. I host it on Heroku, which uses postgresql as db.

I need to extract top scores from the scores table. The table has score and user_id . It worked in mysql with the following:

Score.order('score DESC').group('user_id').limit(25)

This estimates the rating of each user.

When I put the application in Heroku, I get the following psql PGError: ERROR: column "scores.id" must appear in the GROUP BY clause or be used in an aggregate function error PGError: ERROR: column "scores.id" must appear in the GROUP BY clause or be used in an aggregate function

I read, but did not find a clear answer. What is the best way to recreate the above query to work with PostgreSQL?

Thanks!

Tim

Answer 1

This means that your select query selects the id column, but does not include it in the group by clause. I am not familiar with rails, but can he select * or all columns?

Answer 2

Could you do

 select id, max(score) group by id;