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