How to get the latest record from each group in ActiveRecord?

Question

In my Ruby on Rails application I have a database structure like this:

Project.create(:group => "1", :date => "2014-01-01") Project.create(:group => "1", :date => "2014-01-02") Project.create(:group => "1", :date => "2014-01-03")  Project.create(:group => "2", :date => "2014-01-01") Project.create(:group => "2", :date => "2014-01-02") Project.create(:group => "2", :date => "2014-01-03")  # and so forth... 

How can I get the latest record from each group using ActiveRecord?

The solution is probably simple but I can't get my head around this.

Thanks for any help.

Answer 1

Postgres

In Postgres, this can be achieved with the following query.

SELECT DISTINCT ON ("group") * FROM projects ORDER BY "group", date DESC, id DESC 

Because the date column might not be unique here, I have added an additional ORDER BY clause on id DESC to break ties in favor of the record with the higher ID, in case two records in a group have the same date. You might instead want to use another column like the date/time of the last update or so, that depends on your use case.

Moving on, ActiveRecord unfortunately has no API for DISTINCT ON, but we can still use plain SQL with select:

Project.select('DISTINCT ON ("group") *').order(:group, date: :desc, id: :desc) 

or if you prefer using ARel instead of having raw SQL:

p = Project.arel_table Project.find_by_sql(   p.project(p[Arel.star])    .distinct_on(p[:group])    .order(p[:group], p[:date].desc, p[:id].desc) ) 

MySQL

For other databases like MySQL this is unfortunately not as convenient. There are a variety of solutions available, see for example this answer.

Answer 2

I spent some time battling this and thought I'd share what I found to be the cleanest and stunningly easy solution (assuming date or other sorting field contains unique values):

Project.group(:group).maximum(:date) 

Hat tip to qarol for posting this in this comment.