up vote 48 down vote favorite
15

This query 

Message.where("message_type = ?", "incoming").group("sender_number").count

will return me an hash.

OrderedHash {"1234"=>21, "2345"=>11, "3456"=>63, "4568"=>100}

Now I want to order by count of each group. How can I do that within the query.

share|improve this question
up vote 70 down vote accepted

The easiest way to do this is to just add an order clause to the original query. If you give the count method a specific field, it will generate an output column with the name count_{column}, which can be used in the sql generated by adding an order call:

Message.where('message_type = ?', 'incoming').group('sender_number').order('count_id asc').count('id')

share|improve this answer
1  
Nopes its not working for me.. – Mohit Jain Aug 20 '11 at 11:03
1  
Which database engine are you using? Which version of rails, and what's the generated sql? I've just got a test case (different model) working fine on rails 3.0.7... e.g. @test = Price.where('price is not null').group(:price_date).order('count_price asc').count('price'), which generates SELECT COUNT("prices"."price") AS count_price, price_date AS price_date FROM "prices" WHERE (price is not null) GROUP BY price_date ORDER BY count_price asc – Simon Elliston Ball Aug 20 '11 at 11:46
    
Thank you so much for this. This saved me so much time off my query compared to how I was doing it before! Most important lesson: I learned that the count method 'will generate an output column with the name count_{column}'! Thank you! – Yavin4 Apr 7 '14 at 19:48
    
I can't believe this only has 35 (now 36) upvotes. So useful! – jeffdill2 Dec 30 '15 at 19:51
    
Might be worth specifying whcih DB we're using here. This doesn't work for Postgres. whereas @penner's below does. – Carpela Jun 23 '16 at 12:57
up vote 13 down vote

When I tried this, rails gave me this error

SQLite3::SQLException: no such column: count_id: SELECT  COUNT(*) AS count_all, state AS state FROM "ideas"  GROUP BY state ORDER BY count_id desc LIMIT 3

Notice that it says SELECT ... AS count_all

So I updated the query from @Simon's answer to look like this and it works for me

.order('count_all desc')
share|improve this answer
    
Not sure which AR version causes it to use count_all instead of count_id but in 4.2.1, .order("count_id desc") works. – sameers Apr 28 '15 at 23:09
    
Works for me. Maybe you missed the .count('id')? – mahemoff Aug 8 '15 at 13:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.