Re: SQL question on an outer join

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