CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecordmax
Mohit Jain
Mohit Jain

Reputation: 43959

Maximum in group by active record query

I have a Model which has two attributes (I am showing only two as only those two columns are needed)

MyModel

place_id ------------------------ user_id

1 ---------------------------------- 1

1 ---------------------------------- 2

1 ---------------------------------- 2

3 ---------------------------------- 3

3 ---------------------------------- 2

3 ---------------------------------- 3

3 ---------------------------------- 1

I want to fetch group by maximum records. Basically I want to fetch for a particular place which user has maximum records like for for place_id 1 user 2 has maximum results and for place 3 user 3 has maximum results.

Upvotes: 3

Views: 8434

Answers (1)

Kevin Tsoi
Kevin Tsoi

Reputation: 1807

If you are specifying a place first you can do:

MyModel.where("place_id=?",place_id).
        group("place_id,user_id").order("count(user_id) DESC").first

If you want to get all the maximums at once, then remove the where clause and iterate through your result set, taking only the first record for each distinct place_id. Or you may be able to add a distinct to the statement above. Something like this?

MyModel.select("DISTINCT(place_id)").
        merge(MyModel.group("place_id,user_id").order("count(user_id) DESC"))

Good luck!

Upvotes: 6

Related Questions