Question: I have a model called a `Statement` that belongs to a `Member`. Given an array of members, I want to create a query that will return the **most recent** statement for each of those members (preferably in one nice clean query).

I thought I might be able to achieve this using `group` and `order` - something like:

```
# @members is already in an array

@statements = Statement.all(
  :conditions => ["member_id IN(?)", @members.collect{|m| m.id}],
  :group      => :member_id,
  :order      => "created_at DESC"
)

```

But unfortunately the above always returns the *oldest* statement for each member. I've tried swapping the order option round, but alas it always returns the oldest statement of the group rather than the most recent.

I'm guessing `group_by` isn't the way to achieve this - so how do I achieve it?

PS - any non Ruby/Rails people reading this, if you know how to achieve this in raw MySQL, then fire away.

Answer: 
In MySQL directly, you need a sub-query that returns the maximum `created_at` value for each member, which can then be joined back to the `Statement` table to retrieve the rest of the row.

```
SELECT *
FROM Statement s
    JOIN (SELECT 
              member_id, MAX(created_at) max_created_at 
          FROM Statement 
          GROUP BY member_id
    ) latest
        ON  s.member_id  = latest.member_id 
        AND s.created_at = latest.max_created_at

```
If you are using Rails 3 I would recommend taking a look at the new ActiveRecord query syntax. There is an overview at <http://guides.rubyonrails.org/active_record_querying.html>

I am pretty certain you could do what you are trying to do here without writing any SQL. There is an example in the "Grouping" section on that page which looks similar to what you are trying to do.