CODEWITHSUNDEEP
mysqlruby-on-railsrails-activerecord
HHorns
HHorns

Reputation: 11

Rails ActiveRecord: How to query the maximum value of column A and group by column B?

I want to do something like

  Model.select(column_a: :maximum, :column_b).group_by(:column_b)

I am using MySQL 5.6 and rails 5

Upvotes: 0

Views: 4031

Answers (2)

HHorns
HHorns

Reputation: 11

This worked out for me.

Model.select('MAX(column_a) AS column_a', :column_b).group(:column_b)

If you keep the max value as max or some other name that isn't present as an attribute in the model, you won't see the value in the object as rails wouldn't map the result to anything.

Upvotes: 0

Sebastián Palma
Sebastián Palma

Reputation: 33420

You can use your DBMS max function and GROUP BY:

Model.select('MAX(column_a) AS max').group(:column_b)

Or using ActiveRecord::Calculations#maximum method, plus group as well:

Model.group(:column_b).maximum(:column_a)

Although the result is different as it returns a hash in the form as follows:

{ column_b => column_a, ... }

Upvotes: 2

Related Questions