Hi, wondering if someone could help me out with an SQL query. I need to select the most recent row in every group (grouped by group#) group#/name/date(rail''s timestamp) 1/bob/2008-07-30 16:00:43 UTC 2/joe/2008-07-30 16:00:43 UTC 1/john/2008-07-31 16:00:43 UTC 2/ed/2008-07-31 16:00:43 UTC 3/bill/2008-07-31 16:00:43 UTC 3/tom/2008-07-31 18:00:43 UTC So I need: 1/john/2008-07-31 16:00:43 UTC 2/ed/2008-07-31 16:00:43 UTC 3/tom/2008-07-31 18:00:43 UTC Thanks! -- 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 -~----------~----~----~----~------~----~------~--~---
On Thu, Jul 31, 2008 at 12:09 PM, Justin To < rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Hi, wondering if someone could help me out with an SQL query. I need to > select the most recent row in every group (grouped by group#) > > group#/name/date(rail''s timestamp) > 1/bob/2008-07-30 16:00:43 UTC > 2/joe/2008-07-30 16:00:43 UTC > 1/john/2008-07-31 16:00:43 UTC > 2/ed/2008-07-31 16:00:43 UTC > 3/bill/2008-07-31 16:00:43 UTC > 3/tom/2008-07-31 18:00:43 UTC > > So I need: > 1/john/2008-07-31 16:00:43 UTC > 2/ed/2008-07-31 16:00:43 UTC > 3/tom/2008-07-31 18:00:43 UTCYou''re looking to produce this: SELECT * FROM people GROUP BY group_id ORDER BY group_id, `date` DESC So do this: People.find :all, :order => ''`group`, `date` DESC'', :group => :group_id Make sense? -- Tim --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I managed to figure it out: def search(search, page) paginate_by_sql [''SELECT t.* FROM posts t INNER JOIN ( SELECT requirement_id, MAX(created_at) as MaxDate FROM posts GROUP BY requirement_id )t1 ON t1.requirement_id=t.requirement_id AND t1.MaxDate=t.created_at ORDER BY created_at DESC''], :per_page => 15, :page => page end I have one question though... where and how would I include a WHERE clause to find an optional ''search term''? Thanks! -- 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 -~----------~----~----~----~------~----~------~--~---
On Thu, Jul 31, 2008 at 2:50 PM, Justin To <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> where and how would I include a WHERE clause to find an optional ''search > term''?The WHERE clause always comes before GROUP BY / ORDER BY and after the table definition (FROM ... JOIN ... ) So insert your WHERE clause between "t.created_at" and "ORDER BY" -- Tim Gossett --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On Thu, Jul 31, 2008 at 10:48 AM, Shandy Nantz <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > You check out the w3c website for SQL syntax, that''s what I ues a lot: > > http://www.w3schools.com/SQL/For the record, "w3schools.com" has nothing to do with the W3C (World Wide Web Consortium) -- w3.org. Nor does the W3C have anything to say about SQL :-) -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Tim Gossett wrote:> On Thu, Jul 31, 2008 at 2:50 PM, Justin To > <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org >> wrote: > >> where and how would I include a WHERE clause to find an optional ''search >> term''? > > > The WHERE clause always comes before GROUP BY / ORDER BY and after the > table > definition (FROM ... JOIN ... ) > > So insert your WHERE clause between "t.created_at" and "ORDER BY" > > -- > Tim GossettI didn''t mean literally where I would put it =P. I meant, how do I implement it? -- 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 -~----------~----~----~----~------~----~------~--~---
On Thu, Jul 31, 2008 at 4:46 PM, Justin To <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I didn''t mean literally where I would put it =P. I meant, how do I > implement it?Post.paginate :page => params[:page], :per_page => 30, :order => ''group_id, created_at DESC'', :group => :group_id, :conditions => "body LIKE ''%#{search}%''" But you probably want to look into using an actual search engine like ferret or sphinx. -- Tim --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---