Select max within max
Date: Fri, 11 Jul 2008 13:53:31 +1000
Message-ID: <MPG.22e18443a6c3d67798970d@news.x-privat.org>
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 Received on Thu Jul 10 2008 - 22:53:31 CDT