0

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.

Lee
  • 627
  • 1
  • 8
  • 20
  • 1
    If you actually want to get (honestly frightening) mysql like group by results in postgres, see http://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql `SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c` – Will Feb 08 '12 at 08: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 Feb 08 '12 at 12:28

1 Answers1

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.

Timur Sadykov
  • 10,859
  • 7
  • 32
  • 45
  • 1
    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 – Lee Feb 08 '12 at 12:44