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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Andrew Timberlake
2009-Apr-09 08:48 UTC
Re: Getting the top results only by group in a .find statement
On Thu, Apr 9, 2009 at 8:04 AM, tyliong <tyliong-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 > > > >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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Madhusudhan H R
2009-Apr-09 11:18 UTC
Re: Getting the top results only by group in a .find stateme
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 statementAs 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Unfortunaly, .maximum will only give an array not the model instance that I want. -----Original Message----- From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Madhusudhan H R Sent: Thursday, April 09, 2009 7:19 PM To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org Subject: [Rails] Re: Getting the top results only by group in a .find stateme 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 statementAs 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---