Select max within max
in [Oracle Help]
|
From: Geoff Muldoon on 10 Jul 2008 23:53 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 .... I already have code that returns the correct result, I'm just wondering if there is a technique to do this which is more effective/efficient. Current dummy code: SELECT ColX, ColY, ColZ, ... MoreCols FROM SomeTable WHERE (ColX, ColY, ColZ) IN (SELECT A.ColX, A.ColY, max(B.ColZ) FROM (SELECT ColX, max(ColY) ColY FROM SomeTable GROUP BY ColX) A, SomeTable B WHERE A.ColX = B.ColX AND A.ColY = B.ColY GROUP BY A.ColX, A.ColY) AND <other unrelated where conditions>; FWIW, the real code is actually querying an Oracle Change Data Capture subscriber view (ColX is the PK of the source table, ColY is CSCN$ and ColZ is RSID$), and I want to pick up only the last available change values in a change window. Any advice appreciated. Geoff M
From: Carlos on 11 Jul 2008 02:47 On 11 jul, 05:53, Geoff Muldoon <geoff.muld...(a)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 .... > > I already have code that returns the correct result, I'm just wondering if > there is a technique to do this which is more effective/efficient. > > Current dummy code: > SELECT ColX, ColY, ColZ, ... MoreCols > FROM SomeTable > WHERE (ColX, ColY, ColZ) IN > (SELECT A.ColX, A.ColY, max(B.ColZ) > FROM > (SELECT ColX, max(ColY) ColY > FROM SomeTable > GROUP BY ColX) A, > SomeTable B > WHERE A.ColX = B.ColX > AND A.ColY = B.ColY > GROUP BY A.ColX, A.ColY) > AND <other unrelated where conditions>; > > FWIW, the real code is actually querying an Oracle Change Data Capture > subscriber view (ColX is the PK of the source table, ColY is CSCN$ and > ColZ is RSID$), and I want to pick up only the last available change > values in a change window. > > Any advice appreciated. > > Geoff M What about: SELECT ColX, ColY, ColZ, ... MoreCols FROM ( select ColX, ColY, ColZ, ... MoreCols, rank () over (partition by ColX order by colY desc, colZ desc) my_rank from SomeTable ) a where a.my_rank=1 Not tested. (No Oracle at hand) HTH. Cheers. Carlos.
From: Mladen Gogala on 11 Jul 2008 04:38 On Fri, 11 Jul 2008 13:53:31 +1000, Geoff Muldoon 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 .... > > I already have code that returns the correct result, I'm just wondering > if there is a technique to do this which is more effective/efficient. > > Current dummy code: > SELECT ColX, ColY, ColZ, ... MoreCols FROM SomeTable > WHERE (ColX, ColY, ColZ) IN > (SELECT A.ColX, A.ColY, max(B.ColZ) > FROM > (SELECT ColX, max(ColY) ColY > FROM SomeTable > GROUP BY ColX) A, > SomeTable B > WHERE A.ColX = B.ColX > AND A.ColY = B.ColY > GROUP BY A.ColX, A.ColY) > AND <other unrelated where conditions>; > > FWIW, the real code is actually querying an Oracle Change Data Capture > subscriber view (ColX is the PK of the source table, ColY is CSCN$ and > ColZ is RSID$), and I want to pick up only the last available change > values in a change window. > > Any advice appreciated. > > Geoff M My advice would be to re-examine your data model instead of looking for the Mad Max function. -- Mladen Gogala http://mgogala.freehostia.com
From: Charles Hooper on 11 Jul 2008 07:42 On Jul 10, 11:53 pm, Geoff Muldoon <geoff.muld...(a)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: Mladen Gogala on 11 Jul 2008 13:07 On Fri, 11 Jul 2008 04:42:20 -0700, Charles Hooper wrote: > 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; And, if T1 has few thousand rows, it's going to be full table scan, window sort and sort distinct, in that order. In other words, the resource consumption will just explode. As this is obviously a report, materialized view looks like the only reasonable solution. This is a sort of "mad max" query. -- http://mgogala.freehostia.com
|
Next
|
Last
Pages: 1 2 3 Prev: setting currval in a sequence without recreating the sequence Next: nested select problem |