# Using :group and :order with find()

**URL:** <https://www.ruby-forum.com/t/using-group-and-order-with-find/66010>\
**Category:** Rails\
**Created:** [August 22, 2006, 9:32pm UTC](https://www.ruby-forum.com/t/using-group-and-order-with-find/66010 "2006-08-22T21:32:09Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![jfoxny](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a6a055/32.png) [@jfoxny](https://www.ruby-forum.com/u/jfoxny)\
**Post date:** [August 22, 2006, 9:32pm UTC](https://www.ruby-forum.com/t/using-group-and-order-with-find/66010/1 "2006-08-22T21:32:09Z")

</div>

I find that when using both the group and order parameters with find I  
get unexpected results. Perhaps I’m doing this incorrectly OR perhaps  
my expectations just need adjusting. Either way, here’s what I’m doing:

class State \< ActiveRecord::Base  
has\_one :country  
end

class Country \< ActiveRecord::Base  
has\_many :states  
end

State.find(:all, :include =\> :country, :group =\> ‘countries.id’, :order  
=\> ‘states.name’)

I expected to have received a collection of states grouped by country  
and ordered by the state’s name _within each group_. Instead I received  
a collection that was just ordered by the state’s name. Is there  
anyway, besides the obvious find\_by\_sql DIY approach to get my desired  
results using find?

Regards,  
Jason

---

<div class="post-metadata">

**Author:** ![jfoxny](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a6a055/32.png) [@jfoxny](https://www.ruby-forum.com/u/jfoxny)\
**Post date:** [August 22, 2006, 9:34pm UTC](https://www.ruby-forum.com/t/using-group-and-order-with-find/66010/2 "2006-08-22T21:34:22Z")

</div>

Jason F. wrote:

Sorry, correction:

class State \< ActiveRecord::Base  
belongs\_to :country  
end

---

<div class="post-metadata">

**Author:** ![jfoxny](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a6a055/32.png) [@jfoxny](https://www.ruby-forum.com/u/jfoxny)\
**Post date:** [August 23, 2006, 9:10am UTC](https://www.ruby-forum.com/t/using-group-and-order-with-find/66010/3 "2006-08-23T09:10:49Z")

</div>

On 8/22/06, Jason F. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> class Country \< ActiveRecord::Base  
> anyway, besides the obvious find\_by\_sql DIY approach to get my desired  
> results using find?

Not quite sure what you’re looking for, but the first thing you might  
want  
to do is  
to declare ‘Country’ as:

class Country \< ActiveRecord::Base  
has\_many :states, :order =\> ‘name’  
end

Then saying

Country.find(:all, :include =\> :state)

will load all the state/country pairs in one query.

The SQL terminology is not 100% transparent - “GROUP BY” is used to  
create  
partial results when using aggregate functions. A simple example would  
be  
using  
SUM() to create totals - without grouping, the result is the total of  
all  
the selected  
rows. With grouping, the result will be a set of subtotals.

Hope this helps,

Matt J.  
removed\_email\_address@domain.invalid  
President/Technical Director, Acme Art Company, [acmeartco.org](http://acmeartco.org)

---

<div class="post-metadata">

**Author:** ![jfoxny](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/a6a055/32.png) [@jfoxny](https://www.ruby-forum.com/u/jfoxny)\
**Post date:** [August 23, 2006, 2:37pm UTC](https://www.ruby-forum.com/t/using-group-and-order-with-find/66010/4 "2006-08-23T14:37:52Z")

</div>

Jason F. wrote:

> I expected to have received a collection of states grouped by country  
> and ordered by the state’s name _within each group_. Instead I received  
> a collection that was just ordered by the state’s name. Is there  
> anyway, besides the obvious find\_by\_sql DIY approach to get my desired  
> results using find?

GROUP BY collects all the result rows with the same countries.id and  
smashes them into a single result row (as Matt said, useful for doing  
aggregate functions, like SUM and COUNT). You want to just ORDER BY  
countries.id, states.name

ASE