SQL

rubyonrails-talk
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

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?

3

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:

Good luck,

-S

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

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”

6

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:

7

Tim Gossett wrote:

8

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.