Getting the top results only by group in a .find statement

rubyonrails-talk
1

This is my table

model name is bids

id | bid | keyword 1 | 2 | orange 2 | 3 | orange 3 | 1 | red 4 | 3 | blue 5 | 6 | red

what i want is the get these the top bidder of each unique keyword in one .find statement

2

assuming the table name is bids

The direct SQL for this would be select bids.* from bids inner join (select max(bid) as bid, keyword from bids group by keyword) bids1 on bids.bid = bids1.bid and bids.keyword = bids1.keyword

in ActiveRecord it can be called as: Model.find_by_sql(<the sql string above>) or Model.find(:all, :select => 'bids.*', :join => ' inner join (select max(bid) as bid, keyword from bids group by keyword) bids1 on bids.bid = bids1.bid and bids.keyword = bids1.keyword') which will allow you to add additional constraints

Andrew Timberlake http://ramblingsonrails.com http://www.linkedin.com/in/andrewtimberlake

"I have never let my schooling interfere with my education" - Mark Twain

3

Tyl Tan wrote:

This is my table

model name is bids

id | bid | keyword 1 | 2 | orange 2 | 3 | orange 3 | 1 | red 4 | 3 | blue 5 | 6 | red

what i want is the get these the top bidder of each unique keyword in one .find statement

As per my knowledge Table name should be in plurals and Model name should be singular. So assuming the Model name to be Bid, you can even do,

Bid.maximum(:bid, :group=>"keyword")

This will return the maximum bids of each unique keyword. The return type will be ActiveSupport::OrderedHash

Regards, Madhusudhan

4

Unfortunaly, .maximum will only give an array not the model instance that I want.