Re: Select max within max

On Fri, 11 Jul 2008 13:53:31 +1000, Geoff Muldoon wrote:

> Hi all,
>
> 10.2.x on Linux ...
>
> Is there a "neater"/more efficient way of selecting the maximum value
> for a column based on the maximum value of another column. Gods that
> reads poorly, I'll try an example ...
>
> SomeTable
> ColX ColY ColZ .... MoreCols
> A 1 8 ....
> A 1 9 ....
> A 3 4 ....
> A 3 6 ....
> ....
> B 7 2 ....
> B 7 7 ....
> B 8 3 ....
> B 8 5 ....
> ....
>
> I want to get all columns for each distinct value in ColX, but only the
> row for the max value of ColY, and the max value of ColZ for that
> combination, ie.:
>
> A 3 6 ....
> B 8 5 ....
>
> I already have code that returns the correct result, I'm just wondering
> if there is a technique to do this which is more effective/efficient.
>
> Current dummy code:
> SELECT ColX, ColY, ColZ, ... MoreCols FROM SomeTable
> WHERE (ColX, ColY, ColZ) IN
> (SELECT A.ColX, A.ColY, max(B.ColZ)
> FROM
> (SELECT ColX, max(ColY) ColY
> FROM SomeTable
> GROUP BY ColX) A,
> SomeTable B
> WHERE A.ColX = B.ColX
> AND A.ColY = B.ColY
> GROUP BY A.ColX, A.ColY)
> AND <other unrelated where conditions>;
>
> FWIW, the real code is actually querying an Oracle Change Data Capture
> subscriber view (ColX is the PK of the source table, ColY is CSCN$ and
> ColZ is RSID$), and I want to pick up only the last available change
> values in a change window.
>
> Any advice appreciated.
>
> Geoff M

My advice would be to re-examine your data model instead of looking for the Mad Max function.

-- 
Mladen Gogala
http://mgogala.freehostia.com