How to approach GROUP BY in PostgreSQL using Rails?

Question

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

I'm trying to use DISTINCT ON as stated by others. Score.select('DISTINCT ON (user_id) id, user_id, score').order('user_id, score DESC').limit(25) and I get the following error: "PGError: ERROR: column id_list.alias_0 does not exist LINE 1: ... AS id_list ORDER BY id_list.al...". I'm not even stating a column with the name of 'id_list' so I don't get why that's coming up. — Tim
– Tim, Commented Dec 28, 2010 at 7:32

blahter · Accepted Answer · 2010-12-28 01:36:46Z

2

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?

answered Dec 28, 2010 at 1:36

blahter

211 bronze badge

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

Comments

Igor · Accepted Answer · 2012-05-15 03:07:13Z

0

Could you do

select id, max(score) group by id;

edited May 15, 2012 at 3:07

Igor

34.3k14 gold badges82 silver badges116 bronze badges

answered Dec 28, 2010 at 6:04

Scott Marlowe

9,0403 gold badges27 silver badges21 bronze badges

4 Comments

Scott Marlowe Over a year ago

I'd like to see the query that got the error, because that query up there should generate no error.

Tim Over a year ago

Score.select('user_id, score, max(score)').group('user_id').limit(25) works with mysql but shoots off the same error with postgresql.

Scott Marlowe Over a year ago

That is not the same as my query, you're asking for score without grouping by it or using an aggregate. make it something like score.select('user_id,max(score)').group('user_id').limit(25) If you need all the scores then a group by isn't the right choice anyway.

Tim Over a year ago

Hey Scott, first of all, thanks for your replies. Your query works but I need to list the scores. If I don't use 'score' in the select statement like I did above, I get the error 'missing attribute: score'. If I call @high_scores = Score.select('user_id,max(score)').group('user_id').limit(25), I can't call @high_score.score. Is there a way to resolve this?

Collectives™ on Stack Overflow

How to approach GROUP BY in PostgreSQL using Rails?

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related