46

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.

Tintin81
  • 9,821
  • 20
  • 85
  • 178
  • Do you want them to be sorted by their `date` field or by the time the records where created? – Agis Jan 22 '14 at 17:19
  • @Agis: By their `date` field. – Tintin81 Jan 22 '14 at 17:21
  • In case anyone else arrives at this page just looking for how to sort by latest record on a single column (no grouping), just do something like ``Project.order(created_at: :desc).first``. – labyrinth Feb 10 '17 at 17:41

6 Answers6

77

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.

Patrick Oscity
  • 53,604
  • 17
  • 144
  • 168
  • 2
    @p11y This is exactly what I was looking for as well, however, it doesn't work in PostgreSQL. I get a PGError - `column "projects.id" must appear in the GROUP BY clause or be used in an aggregate function`. Any ideas? – bcb Aug 19 '14 at 21:53
  • 19
    For **PostgresSQL** you may use `Project.select("DISTINCT ON(group_id) *").order("group_id, date DESC")` – remo Feb 26 '15 at 15:26
  • Does'nt this only work if all the latest record have the same date value? – Alain Sep 11 '15 at 13:01
  • 12
    @Alain It does. I think the better solution is somethink like this: `Project.group("group").maximum(:date)`. It should return the max date for each group without omitting any group. – qarol Dec 22 '15 at 12:39
  • @Alain @qarol which query are you talking about? Both in mine and @remo's queries the `max` should apply only within a group. – Patrick Oscity Dec 22 '15 at 13:45
  • @qarol you should make that comment an answer - it's actually the best solution (IMO) – Yule Sep 14 '16 at 16:42
  • @PatrickOscity I tried out `having` but it just returns the record with the latest date from the array returned by `group`. `group` returns an array of the first record from each group. – carl Jan 25 '18 at 09:07
  • @carlmyerflor (and others before you) you're absolutely right, my bad. I remember I originally tried this out but overlooked that the maximum date is the same for all groups in the test data from the OP. Therefore my original approach _appeared_ to work but it doesn't. With Postgres, `DISTINCT ON` is definitely the way to go as @remo suggested. In MySQL and other DBs this is unfortunately more complicated to achieve. Editing my answer to reflect this. – Patrick Oscity Jan 25 '18 at 21:27
13

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.

jstafford
  • 418
  • 6
  • 14
  • 4
    It is definitely simple, but question was "How can I get the latest record". If user needs to extend this answer to include other fields, it's a problem. – jing Jun 23 '21 at 09:02
6

This works for me

ids = Message.select("MAX(id) AS id").group(:column_name).collect(&:id)
@result = Message.order("created_at DESC").where(:id => ids)
Navid Farjad
  • 435
  • 5
  • 16
  • 1
    Be careful with this solution. If DB returns more than ~1'000 ids, this could lead to very long duration query. – jing Jun 23 '21 at 09:12
5

Following solution based on this link works for MySQL and it's extensible to all fields in group table.

Project.select(:group, 'MAX(date) AS date').group(:group)
jing
  • 1,919
  • 2
  • 20
  • 39
-6

Something like this?

Project.select(:group).map(&:group).uniq.each do |grp|
  puts Project.where(group: grp).order("date DESC").last
end

This will go through all your groups and identify the unique ones. In your example it should return ["1", "2"]. Then it iterates over that array and selects the last Project with a group id of 1 and the last Project with a group id of 2.

** Update **

Just realized you said "latest" and not "last" which required adding an order to ensure latest works. Last still pulls just one.

AdamT
  • 6,405
  • 10
  • 49
  • 75
  • this will also retrieve *all records* from the database, then filter them in memory and finally make another query for each group. – Patrick Oscity Jan 22 '14 at 17:38
-11
Project.where(:group => "1", :date => "2014-01-01").last

.last is what you are looking for.

nzajt
  • 3
  • 4