Sql

Rails
#1

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!

#2

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:

http://www.w3schools.com/SQL/

Good luck,

-S

#3

On Thu, Jul 31, 2008 at 12:09 PM, Justin To <
[email protected]> 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

#4

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!

#5

On Thu, Jul 31, 2008 at 2:50 PM, Justin To
<[email protected]

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.

#6

On Thu, Jul 31, 2008 at 10:48 AM, Shandy N.
[email protected] 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 :slight_smile:

–
Hassan S. ------------------------ [email protected]

#7

Tim G. wrote:

On Thu, Jul 31, 2008 at 2:50 PM, Justin To
<[email protected]

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?

#8

On Thu, Jul 31, 2008 at 4:46 PM, Justin To
<[email protected]

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