Basic SQL problem

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 Received on Fri Mar 09 2007 - 07:34:52 CST