Select max within max

From: Geoff Muldoon <>
Date: Fri, 11 Jul 2008 13:53:31 +1000
Message-ID: <>

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 ...


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)

     (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

Original text of this message