Re: SQL question on an outer join
Date: Sun, 11 May 2008 06:05:01 -0700 (PDT)
Message-ID: <bf0e27aa-20e0-4294-b0a4-ba4599436b12@q24g2000prf.googlegroups.com>
On May 11, 12:07 am, fergus <fergus_v..._at_yahoo.com> wrote:
> On May 10, 11:21 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > Thanks for posting the DDL and DML for the setup.
>
> > Using the suggestion offered by Pat, with a small modification:
> > SELECT
> > TABLEA.COLA,
> > TABLEB.COLA_FK,
> > TABLEB.COLC
> > FROM
> > TABLEA
> > LEFT JOIN
> > TABLEB
> > ON
> > TABLEA.COLA=TABLEB.COLA_FK
> > AND TABLEB.COLC=99999;
>
> > COLA COLA_FK COLC
> > ---------- ---------- ----------
> > 2 2 99999
> > 1
>
> > This is the way I would commonly set up a SQL statement to meet a
> > similar requirements:
> > SELECT
> > TABLEA.COLA,
> > TABLEB.COLA_FK,
> > TABLEB.COLC
> > FROM
> > TABLEA,
> > TABLEB
> > WHERE
> > TABLEA.COLA=TABLEB.COLA_FK(+)
> > AND TABLEB.COLC(+)=99999;
>
> > COLA COLA_FK COLC
> > ---------- ---------- ----------
> > 1
> > 2 2 99999
>
> > Will the value of interest always be 99999, or will it be the highest
> > value with a matching COLA_FK? If you are looking for the highest
> > value, please supply the four digit version of Oracle that you are
> > using (10.2.0.2, 11.1.0.6, 8.1.7.3, etc.).
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Aha - that is very cool. I had figured out the
> > TABLEA.COLA=TABLEB.COLA_FK(+)
>
> but did not know I could do this:
>
> > AND TABLEB.COLC(+)=99999;
>
> As a matter of fact you are right - how did you guess - in my cases,
> the interest of value would be max of whatever
> is in TABLEB.COLC - if the row with 99999 does not exist, then the
> sql returns the fow with data in COLC=12345.
> Also, working with ORACLE version 9.2.0.8.
>
> Thank you once again,
>
> Regards,
> Fergus
It looks like the DENSE_RANK analytical function, an inline view, and an outer join are required.
First, let's introduce a little more data to make certain that we cannot query for a specific value of COLC and return the expected results:
INSERT INTO TABLEA VALUES (3); INSERT INTO TABLEB VALUES (13,2,111111); INSERT INTO TABLEB VALUES (13,3,11);
Next, we try an experiment with the DENSE_RANK function to separate
the rows by the value of COLA_FK (caused by the PARTITION BY
directive) and rank the values sorted from highest to lowest (caused
by the DESC directive):
SELECT
COLB,
COLA_FK,
COLC,
DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
FROM
TABLEB;
COLB COLA_FK COLC DR
---------- ---------- ---------- ----------
13 2 111111 1 12 2 99999 2 11 2 12345 3 13 3 11 1
We are only interested in the rows with DR = 1, so we need a way to
eliminate the unnecessary rows. If we slide the above SQL statement
into an inline view, we are able to add a WHERE clause that restricts
the results to the rows containing the highest COLC value per COLA_FK
value. We can then alias the inline view (as B), and join it to
TABLEA as before:
SELECT
TABLEA.COLA,
B.COLA_FK,
B.COLC
FROM
TABLEA,
(SELECT
COLB,
COLA_FK,
COLC,
DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
FROM
TABLEB) B
WHERE
TABLEA.COLA=B.COLA_FK(+)
AND B.DR(+)=1;
COLA COLA_FK COLC
---------- ---------- ----------
2 2 111111 3 3 11 1
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun May 11 2008 - 08:05:01 CDT