# SQL **URL:** \ **Category:** rubyonrails-talk\ **Created:** [July 31, 2008, 4:09pm UTC](https://discuss.rubyonrails.org/t/sql/32621 "2008-07-31T16:09:58Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\ **Post date:** [July 31, 2008, 4:09pm UTC](https://discuss.rubyonrails.org/t/sql/32621/1 "2008-07-31T16:09:58Z")
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! ---
**Author:** ![Tim\_Gossett1](https://avatars.discourse-cdn.com/v4/letter/t/6a8cbe/32.png) [@Tim\_Gossett1](https://discuss.rubyonrails.org/u/Tim_Gossett1)\ **Post date:** [July 31, 2008, 5:26pm UTC](https://discuss.rubyonrails.org/t/sql/32621/2 "2008-07-31T17:26:42Z")
You’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? ---
**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\ **Post date:** [July 31, 2008, 5:48pm UTC](https://discuss.rubyonrails.org/t/sql/32621/3 "2008-07-31T17:48:16Z")
Justin To 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 UTC > > Thanks! This might work - keep in mind that I am not working with any dates and therefore have no way to test, but. . . Object.find(:first, :conditions =\> ['group# = ? and ORDER BY date DESC', group#]) If you are trying to do it in pure SQL is would be something like: SELECT FIRST(date) FROM table\_name where group# = '1' and ORDER BY date DESC Again this may or may not be correct but hopefully it gives you a start. You check out the w3c website for SQL syntax, that's what I ues a lot: > **[SQL Tutorial](https://www.w3schools.com/SQL/)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. Good luck, -S ---
**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\ **Post date:** [July 31, 2008, 6:50pm UTC](https://discuss.rubyonrails.org/t/sql/32621/4 "2008-07-31T18:50:08Z")
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! ---
**Author:** ![Tim\_Gossett1](https://avatars.discourse-cdn.com/v4/letter/t/6a8cbe/32.png) [@Tim\_Gossett1](https://discuss.rubyonrails.org/u/Tim_Gossett1)\ **Post date:** [July 31, 2008, 8:13pm UTC](https://discuss.rubyonrails.org/t/sql/32621/5 "2008-07-31T20:13:51Z")
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” ---
**Author:** ![hassan](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/hassan/32/714_2.png) [@hassan](https://discuss.rubyonrails.org/u/hassan)\ **Post date:** [July 31, 2008, 8:31pm UTC](https://discuss.rubyonrails.org/t/sql/32621/6 "2008-07-31T20:31:00Z")
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 🙂 ---
**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\ **Post date:** [July 31, 2008, 8:46pm UTC](https://discuss.rubyonrails.org/t/sql/32621/7 "2008-07-31T20:46:31Z")
Tim Gossett wrote: ---
**Author:** ![Tim\_Gossett1](https://avatars.discourse-cdn.com/v4/letter/t/6a8cbe/32.png) [@Tim\_Gossett1](https://discuss.rubyonrails.org/u/Tim_Gossett1)\ **Post date:** [July 31, 2008, 9:02pm UTC](https://discuss.rubyonrails.org/t/sql/32621/8 "2008-07-31T21:02:37Z")
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.