Re: Basic SQL problem

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 12 Mar 2007 08:32:48 -0700
Message-ID: <1173713569.370390@bubbleator.drizzle.com>

mikeyb wrote:

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

WITH qb AS (SELECT MAX(price_ever) FROM <table_name>) ...

http://www.psoug.org
click on Morgan's Library
click on WITH clause

then complete by joining with qb and selecting ORDER_NUMBER and MAX(<sale_date>) WHERE rownum = 1.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Mar 12 2007 - 10:32:48 CDT