Re: Select max within max

On 11 jul, 05:53, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> 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

What about:

SELECT ColX, ColY, ColZ, ... MoreCols
FROM ( select ColX, ColY, ColZ, ... MoreCols, rank () over (partition by ColX order by colY desc, colZ desc) my_rank from SomeTable ) a where a.my_rank=1

Not tested.

(No Oracle at hand)

HTH. Cheers.

Carlos. Received on Fri Jul 11 2008 - 01:47:30 CDT