# Re: Select max within max

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 11 Jul 2008 19:37:39 +0200
Message-ID: <48779A63.7020507@gmail.com>

Charles Hooper schrieb:
> 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)
```>> Any advice appreciated.
>>
>> 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.

From the very first look, i would probably write this query as

select colx,
max(coly) keep(dense_rank last order by coly,colz) , max(colz) keep(dense_rank last order by coly,colz) from t1
group by colx

but, if i remember correctly, Mladen has shown some weeks ago (i still had no time to investigate it deeper, though it is pretty interesting thema) an example where analytics were substantially slower/less efficient compared to aggregates, due to optimization by hash group by, so i would probably thoroughly test all alternatives in regard of performance, especially with significant data amounts....

Best regards

Maxim Received on Fri Jul 11 2008 - 12:37:39 CDT

Original text of this message