Group by or Select Distinct with Rails, ActiveRecord, Heroku and PostgreSQL

Question

I have a table of records;

Users: id, screen_name, tweet

I want to return a list of the users and group by the screen_name so that there aren't duplicate screen_names in the list.

@users = User.all(:group => "screen_name")

This works fine when using MySQL, but not when I push to Heroku, which uses PostgreSQL.

How can I get a similar set of results using PostgreSQL?

There seem to be a good few posts on this but I couldn't figure out an answer from the comments.

If you actually want to get (honestly frightening) mysql like group by results in postgres, see stackoverflow.com/questions/1769361/… SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c — Will
– Will, Commented Feb 8, 2012 at 8:40
Thanks Will. I tried @users = User.all(:select => "DISTINCT ON (screen_name) screen_name, tweet" but the log showed the error "ActiveRecord::StatementInvalid (PGError: ERROR: column id_list.alias_0 does not exist" — Lee
– Lee, Commented Feb 8, 2012 at 12:28

Timur Sadykov · Accepted Answer · 2012-02-08 07:04:20Z

2

Unless screen_name is not a primary key you should add all columns in your group statement or aggregate them. But it looks like that you can't aggregate id or tweets ))

.group("id, screen_name, tweet")

You can also normalize your database. Put screen names to another table and you will be able to get desired data easily.

edited Feb 8, 2012 at 7:04

answered Feb 8, 2012 at 6:56

Timur Sadykov

11.6k7 gold badges35 silver badges45 bronze badges

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

1 Comment

Lee Over a year ago

I tried adding all fields to the group @users = User.all(:select => "screen_name,tweet", :group => "screen_name,tweet" but unfortunately that seems to return all the records (including duplicates of screen_name). Would like to try and avoid normalizing if possible

Collectives™ on Stack Overflow

Group by or Select Distinct with Rails, ActiveRecord, Heroku and PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related