Rails - Jun 2005 - The rows holding the group-wise maximum

I am looking for opinions on the best way to structure a query. I have
two tables - one "stock" and one "prices". Stock holds the
basic info
on a stock (ie. "Google") and prices holds all the daily prices of the
stocks. I want my query to get the name of all the stocks and their
last trade price.

If I do a query like: 

SELECT stocks.*, prices.close
 FROM products
JOIN prices ON stocks.id = prices.stockt_id
GROUP BY stocks.id

It gives me the first closing price from the prices table - I need the last.

I was wondering what the most efficient way to accomplish my goals is.
I figure I have a few choices.

1. I can set a default "order" in my prices model so it sorts on
"created_on DESC". I read about this somewhere else but I now
can''t
find the documentation on how. I presume it was something like: order
:"created_on DESC". Anyone know how I do this?

2. I can nest a sub-query as explained on this mysql page:
http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

3. Or structure a query recommended in the comments on the above mysql page:
SELECT DISTINCTROW jobs.worker
FROM jobs LEFT JOIN jobs AS s2
ON jobs.worker = s2.worker AND jobs.jobdate < s2.jobdate
WHERE s2.worker IS NULL
ORDER BY inspect.date ASC;

Any suggestions?

Thanks very much,

Steve

What I ended up going with was option 1 below; setting a default sort
order in my model like so:

class Price < ActiveRecord::Base
  belongs_to :stock, :order => ''created_on DESC''
end

Once again, rails makes it easy.

Steve

On 6/2/05, Steve Odom <steve.odom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I am looking for opinions on the best way to structure a query. I have
> two tables - one "stock" and one "prices". Stock holds
the basic info
> on a stock (ie. "Google") and prices holds all the daily prices
of the
> stocks. I want my query to get the name of all the stocks and their
> last trade price.
> 
> If I do a query like:
> 
> SELECT stocks.*, prices.close
>  FROM products
> JOIN prices ON stocks.id = prices.stockt_id
> GROUP BY stocks.id
> 
> It gives me the first closing price from the prices table - I need the
last.
> 
> I was wondering what the most efficient way to accomplish my goals is.
> I figure I have a few choices.
> 
> 1. I can set a default "order" in my prices model so it sorts on
> "created_on DESC". I read about this somewhere else but I now
can''t
> find the documentation on how. I presume it was something like: order
> :"created_on DESC". Anyone know how I do this?
> 
> 2. I can nest a sub-query as explained on this mysql page:
> http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html
> SELECT article, dealer, price
> FROM   shop s1
> WHERE  price=(SELECT MAX(s2.price)
>               FROM shop s2
>               WHERE s1.article = s2.article);
> 
> 3. Or structure a query recommended in the comments on the above mysql
page:
> SELECT DISTINCTROW jobs.worker
> FROM jobs LEFT JOIN jobs AS s2
> ON jobs.worker = s2.worker AND jobs.jobdate < s2.jobdate
> WHERE s2.worker IS NULL
> ORDER BY inspect.date ASC;
> 
> Any suggestions?
> 
> Thanks very much,
> 
> Steve
>