Select max within max
Date: Fri, 11 Jul 2008 13:53:31 +1000
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 ...
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
WHERE (ColX, ColY, ColZ) IN
(SELECT A.ColX, A.ColY, max(B.ColZ)
(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 Received on Thu Jul 10 2008 - 22:53:31 CDT