CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-3
Tim
Tim

Reputation: 1461

How to approach GROUP BY in PostgreSQL using Rails?

I have a Rails 3 app which keeps high scores. I'm hosting it on Heroku which uses postgresql as the db.

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

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

This ranks each user's top score.

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

I've read around but haven't found a clear answer. What is the most optimal way to recreate the above query to work with PostgreSQL?

Thanks!

Tim

Upvotes: 6

Views: 1410

Answers (2)

Scott Marlowe
Scott Marlowe

Reputation: 8870

Could you do

select id, max(score) group by id;

Upvotes: 0

blahter
blahter

Reputation: 21

That means your select query is selecting the "id" column but not including it in the group by clause. I'm not familiar with rails but could it be selecting * or all columns?

Upvotes: 2

Related Questions