CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlruby-on-rails-3.1ruby-on-rails-3.2
loganathan
loganathan

Reputation: 6136

Sort group of records based on different columns

I have a four columns in table called recordings such as score, event_id, user_id and I would like to sum the score of recordings based on user id + event id, and then I would like to show the users of the different events sorted by Score.

So I have wriiten the below code

Recording.select("user_id,event_id,sum(score) as score").group(:user_id,:event_id).order("score DESC")

and the above query outputs the records based on the max score too min score like below

 #<Recording user_id: 4, event_id: 51, score: 64622>,
 #<Recording user_id: 3, event_id: 51, score: 5912>
 #<Recording user_id: 4, event_id: 31, score: 62>, 
 #<Recording user_id: 3, event_id: 48, score: 0>

But I want the re arrabge the results like below (users of the different events sorted by Score)

 #<Recording user_id: 4, event_id: 51, score: 64622>,
 #<Recording user_id: 4, event_id: 31, score: 62>, 
 #<Recording user_id: 3, event_id: 48, score: 0>,
 #<Recording user_id: 3, event_id: 51, score: 5912>

Upvotes: 0

Views: 139

Answers (1)

PinnyM
PinnyM

Reputation: 35533

For PostgreSQL, you can use a windowing function to do this:

select_sql <<-SELECT
  user_id, event_id, sum(score) as score, 
  ROW_NUMBER() OVER (PARTITION BY user_id, event_id ORDER BY sum(score) DESC) as rowNum
SELECT

subquery = Recording.select(select_sql).group(:user_id, :event_id).to_sql

Recording.select("*").from(Arel.sql("(#{subquery}) t")).
          order("rowNum, score DESC")

Upvotes: 1

Related Questions