Sql

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!

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

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

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!

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.

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]

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?

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs