# Sql

**URL:** <https://www.ruby-forum.com/t/sql/144639>\
**Category:** Rails\
**Created:** [July 31, 2008, 6:09pm UTC](https://www.ruby-forum.com/t/sql/144639 "2008-07-31T18:09:58Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 6:09pm UTC](https://www.ruby-forum.com/t/sql/144639/1 "2008-07-31T18:09:58Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 7:48pm UTC](https://www.ruby-forum.com/t/sql/144639/2 "2008-07-31T19:48:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 8:38pm UTC](https://www.ruby-forum.com/t/sql/144639/3 "2008-07-31T20:38:12Z")

</div>

On Thu, Jul 31, 2008 at 12:09 PM, Justin To \<  
removed\_email\_address@domain.invalid\> wrote:

> 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

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?

–  
Tim

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 8:50pm UTC](https://www.ruby-forum.com/t/sql/144639/4 "2008-07-31T20:50:08Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 10:15pm UTC](https://www.ruby-forum.com/t/sql/144639/5 "2008-07-31T22:15:27Z")

</div>

On Thu, Jul 31, 2008 at 2:50 PM, Justin To  
\<removed\_email\_address@domain.invalid

> 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 G.

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 10:31pm UTC](https://www.ruby-forum.com/t/sql/144639/6 "2008-07-31T22:31:50Z")

</div>

On Thu, Jul 31, 2008 at 10:48 AM, Shandy N.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> You check out the w3c website for SQL syntax, that’s what I ues a lot:
> 
> [SQL Tutorial](http://www.w3schools.com/SQL/)

For the record, “[w3schools.com](http://w3schools.com)” has nothing to do with the W3C  
(World Wide Web Consortium) – [w3.org](http://w3.org). Nor does the W3C have  
anything to say about SQL 🙂

–  
Hassan S. ------------------------ removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 10:46pm UTC](https://www.ruby-forum.com/t/sql/144639/7 "2008-07-31T22:46:31Z")

</div>

Tim G. wrote:

> On Thu, Jul 31, 2008 at 2:50 PM, Justin To  
> \<removed\_email\_address@domain.invalid
> 
> > 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 G.

I didn’t mean literally where I would put it =P. I meant, how do I  
implement it?

---

<div class="post-metadata">

**Author:** ![jturney](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e99b99/32.png) [@jturney](https://www.ruby-forum.com/u/jturney)\
**Post date:** [July 31, 2008, 11:22pm UTC](https://www.ruby-forum.com/t/sql/144639/8 "2008-07-31T23:22:48Z")

</div>

On Thu, Jul 31, 2008 at 4:46 PM, Justin To  
\<removed\_email\_address@domain.invalid

> 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