CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Obromios
Obromios

Reputation: 16373

How do I find the records with the maximum value for a particular field in a group

I have a graphs table which has fields name and version. Each time a record is inserted the version is 0 if name does not exist in the database and max(version) + 1 otherwise. I'd like to get list of records with the highest version for each unique name. The following code does this:

def self.latest_versions
   Graph.group(:name).maximum(:version).map{ |k,v|  find_by name: k, version: v}
end

but I wonder if there is a much faster way to do this using a scope that just chains active record relations?

Upvotes: 1

Views: 55

Answers (1)

seaify - Freelancer
seaify - Freelancer

Reputation: 748

How about this

def self.latest_versions
  name_versions = Graph.group(:name).maximum(:version).map {|k,v| [k,v].join(';')}
  Graph.where("concat(name, ';', version) IN (?)", name_versions)
end

after get the name, version groups, convert it into an array, which concat the 2 column into 1, by query through the composed column, we only need another sql query, totally 2, much better than the existed one.

Upvotes: 1

Related Questions