Re: Select max within max
Date: Fri, 11 Jul 2008 04:42:20 -0700 (PDT)
Message-ID: <43fe5910-e619-465f-a69c-fcccebbbea0d@a70g2000hsh.googlegroups.com>
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