# Re: Select max within max

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 11 Jul 2008 04:42:20 -0700 (PDT)

On Jul 10, 11:53 pm, 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     ....

(Snip)
>
> Geoff M

Let's try a couple tests. First the DDL and DML for the tests: CREATE TABLE T1(
COLX VARCHAR2(5),
COLY NUMBER(5),
COLZ NUMBER(5));

```INSERT INTO T1 VALUES ('A',1,8);
INSERT INTO T1 VALUES ('A',1,9);
INSERT INTO T1 VALUES ('A',3,4);
INSERT INTO T1 VALUES ('A',3,6);
INSERT INTO T1 VALUES ('B',7,2);
INSERT INTO T1 VALUES ('B',7,7);
INSERT INTO T1 VALUES ('B',8,3);
INSERT INTO T1 VALUES ('B',8,5);

```

Now the first test to see what happens with the MAX analytical function:
SELECT
COLX,
MAX(COLY) OVER (PARTITION BY COLX) COLY,   COLZ
FROM
T1;

COLX COLY COLZ
----- ---------- ----------

```A              3          8
A              3          9
A              3          4
A              3          6
B              8          2
B              8          7
B              8          3
B              8          5

```

The above looks interesting, let's see if it will work by sliding the above into an inline view and then picking up the maximum value for COLZ:
SELECT DISTINCT
COLX,
COLY,
MAX(COLZ) OVER (PARTITION BY COLX, COLY) COLZ FROM
(SELECT
COLX,
MAX(COLY) OVER (PARTITION BY COLX) COLY,     COLZ
FROM
T1);

COLX COLY COLZ
----- ---------- ----------

```B              8          7
A              3          9

```

Slight problem with COLZ in the above. Let's try again, this time partitioning by two columns from the start: SELECT
COLX,
COLY OLD_COLY,
MAX(COLY) OVER (PARTITION BY COLX) COLY,   MAX(COLZ) OVER (PARTITION BY COLX, COLY) COLZ FROM
T1;

COLX OLD_COLY COLY COLZ
----- ---------- ---------- ----------

```A              1          3          9
A              1          3          9
A              3          3          6
A              3          3          6
B              7          8          7
B              7          8          7
B              8          8          5
B              8          8          5

```

Now to slide the above into an inline view and pick out only those rows where COLZ originally existed within a matching COLY value: SELECT DISTINCT
COLX,
COLY,
COLZ
FROM
(SELECT
COLX,
COLY OLD_COLY,
MAX(COLY) OVER (PARTITION BY COLX) COLY,     MAX(COLZ) OVER (PARTITION BY COLX, COLY) COLZ   FROM
T1)
WHERE
OLD_COLY=COLY; COLX COLY COLZ
----- ---------- ----------

```B              8          5
A              3          6

```

Let's test the output of the SQL statement provided by Carlos with my test table:
SELECT ColX, ColY, ColZ
FROM ( select ColX, ColY, ColZ, rank () over (partition by ColX order by colY desc, colZ desc) my_rank from T1 ) a where a.my_rank=1;

COLX COLY COLZ
----- ---------- ----------

```A              3          6
B              8          5

```

It looks like the SQL that Carlos provided works with the sample data also.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jul 11 2008 - 06:42:20 CDT

Original text of this message