Re: Basic SQL problem

From: Ed Prochak <edprochak_at_gmail.com>
Date: 9 Mar 2007 06:28:51 -0800
Message-ID: <1173450531.049347.56790@t69g2000cwt.googlegroups.com>

On Mar 9, 8:34 am, "mikeyb" <bami..._at_hotmail.com> wrote:
> I am continuallly running into a problem when querying an Oracle 7
> ( I know :-( ) database and trying to pick out particular records in
> a table, I've googled this to there and back and not found anything to
> get me moving forward.
>
> Say I have a table with fields "Sales Order Number" "Price" and "Date"
> and I want to pick out these 3 fields for the highest SO# with the
> most recent highest price.
>
> I know I can write:
>
> select max(table.SO), table.price, table.date
> from table,
> (
> select max(table.date) date,
> table.price
> from table,
> (
> select max(table.Price) price
> from table
> ) sq2
> where sq2.price = table.price
> group by table.price
> ) sq
> where sq.price = table.price
> and sq.date = table.date
> group by table.price, table.date
>
> So pick out the max price from the table then look through the table
> again for the max date with that price then again for the max SO with
> the most recent max price.
>
> This seems like a very intensive way of doing something that seems
> simple. I'm far from an expert at this but this would seem to look
> through the whole table 3 times which is slow when the table is
> big.... which of course it is!
>
> Can anyone give me a pointer that can tidy this clumsy mess up?
>
> Thanks
>
> Mike

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 Received on Fri Mar 09 2007 - 08:28:51 CST