Re: Select max within max
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