ruby on rails - 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.