Re: Select max within max

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. Received on Fri Jul 11 2008 - 06:42:20 CDT