# Combining :order and :group in find yields wrong records

**URL:** <https://www.ruby-forum.com/t/combining-order-and-group-in-find-yields-wrong-records/152549>\
**Category:** Rails\
**Created:** [November 5, 2008, 3:37am UTC](https://www.ruby-forum.com/t/combining-order-and-group-in-find-yields-wrong-records/152549 "2008-11-05T03:37:34Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\
**Post date:** [November 5, 2008, 3:37am UTC](https://www.ruby-forum.com/t/combining-order-and-group-in-find-yields-wrong-records/152549/1 "2008-11-05T03:37:34Z")

</div>

I have a setup with Members, Teams and Memberships.

Teams can have\_many Memberships  
Teams can have\_many Members through Memberships

Memberships belongs to a status

Only the latest Membership of a Member is valid.

I use this setup to keep a sort of audit log of a Member’s history so I  
can see which teams he applied to, where he was kicked from and which he  
joined in. The Memberships table is timestamped and most of the  
distinction is done based on the created\_at column.

Memberships has the following columns:  
id team\_id member\_id membership\_status\_id created\_at updated\_at

Now I would like a list for all team with their pending memberships.  
(defined by the membership\_status\_id)

I run this query:  
Membership.find(:all, :conditions =\> {:membership\_status\_id =\> PENDING})

This returns a list of all Memberships that HAVE EVER BEEN PENDING. So  
this also yields records that are long accepted or declined. Not really  
useful!

To be more precise, I want for all members their latest membership to  
match the PENDING condition.

Back to the drawing board, I designed the next query:  
Membership.find(:all, :group =\> ‘member\_id’, :order =\> ‘created\_at  
DESC’)

Now this actually groups correctly on the member ID and only gives me  
back 1 membership for each member. But it will give me the FIRST one.  
The order clause is completely ignored. How do I get the LAST one with a  
group\_by part? My SQL is kind of rusty in that department.

---

<div class="post-metadata">

**Author:** ![chrisdwan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7feea3/32.png) [@chrisdwan](https://www.ruby-forum.com/u/chrisdwan)\
**Post date:** [November 5, 2008, 12:25pm UTC](https://www.ruby-forum.com/t/combining-order-and-group-in-find-yields-wrong-records/152549/2 "2008-11-05T12:25:53Z")

</div>

On 5 Nov 2008, at 02:37, Chris D. wrote:

> I use this setup to keep a sort of audit log of a Member’s history  
> (defined by the membership\_status\_id)  
> To be more precise, I want for all members their latest membership to  
> group\_by part? My SQL is kind of rusty in that department.  
> The order clause isn’t ignored - it’s used for sorting the final array  
> (or to put things another way it does not sort then group). When you  
> group by something, all non aggregate columns (ie things other than  
> things like SUM, COUNT) are indeterminate. If you are using mysql see  
> also  
> [http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html](http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html)

Fred