Re: Basic SQL problem

>
> I'm a little confused. What really is "the most recent max price"???
> from your query you get the MAX price independent of the date, so if
> that is the correct price, and you do not use the MAX date from the
> middle in-line view, then remove that view:
>
> select max(table.SO), table.price, table.date
> from table,
> (
> select max(table.Price) price
> from table
> ) sq2
> where sq2.price = table.price
> group by table.price, table.date
>
> Otherwise there is something in the requirement that I seem to be
> missing.
>
> Ed

I think maybe my english is more confusing than the sql !!

Inevitably I was trying to illustrate the type of problem I am having with an example which maybe hasn't worked terribly well, sorry about that.

But I think the sql I wrote does represent the problem. In the example I pick out the maximum price (ever), then find the most recent date that this occured, then (assume lots of sales per day) pick out the maximum sales order number record. To me this takes three trips through the table which seems like hard work and is very time consuming in my real query.

thanks for your interest!

Mike Received on Mon Mar 12 2007 - 04:58:47 CDT