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

Rails
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

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

3

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

4

On Thu, Apr 9, 2009 at 8:04 AM, tyliong [email protected] wrote:

5 | 6 | red

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

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()
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 T.
http://ramblingsonrails.com
http://www.linkedin.com/in/andrewtimberlake

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