Forum: Ruby on Rails Getting the top results only by group in a .find statement

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Tyl T. (Guest)
on 2009-04-09 10:05
(Received via mailing list)
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
Andrew T. (Guest)
on 2009-04-09 12:49
(Received via mailing list)
On Thu, Apr 9, 2009 at 8:04 AM, tyliong <removed_email_address@domain.invalid> 
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(<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 T.
http://ramblingsonrails.com
http://www.linkedin.com/in/andrewtimberlake

"I have never let my schooling interfere with my education" - Mark Twain
Madhusudhan H. (Guest)
on 2009-04-09 15:18
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
Tyl T. (Guest)
on 2009-04-11 04:05
(Received via mailing list)
Unfortunaly, .maximum will only give an array not the model instance
that I want.
This topic is locked and can not be replied to.