Re: SQL question on an outer join

On May 11, 12:07=A0am, fergus <fergus_v...@[EMAIL PROTECTED]
> wrote:
> On May 10, 11:21=A0am, Charles Hooper <hooperc2...@[EMAIL PROTECTED]
> wrote:
> > Thanks for posting the DDL and DML for the setup.
>
> > Using the suggestion offered by Pat, with a small modification:
> > SELECT
> > =A0 TABLEA.COLA,
> > =A0 TABLEB.COLA_FK,
> > =A0 TABLEB.COLC
> > FROM
> > =A0 TABLEA
> > LEFT JOIN
> > =A0 TABLEB
> > ON
> > =A0 TABLEA.COLA=3DTABLEB.COLA_FK
> > =A0 AND TABLEB.COLC=3D99999;
>
> > =A0 =A0 =A0 COLA =A0 =A0COLA_FK =A0 =A0 =A0 COLC
> > ---------- ---------- ----------
> > =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A099999
> > =A0 =A0 =A0 =A0 =A01
>
> > This is the way I would commonly set up a SQL statement to meet a
> > similar requirements:
> > SELECT
> > =A0 TABLEA.COLA,
> > =A0 TABLEB.COLA_FK,
> > =A0 TABLEB.COLC
> > FROM
> > =A0 TABLEA,
> > =A0 TABLEB
> > WHERE
> > =A0 TABLEA.COLA=3DTABLEB.COLA_FK(+)
> > =A0 AND TABLEB.COLC(+)=3D99999;
>
> > =A0 =A0 =A0 COLA =A0 =A0COLA_FK =A0 =A0 =A0 COLC
> > ---------- ---------- ----------
> > =A0 =A0 =A0 =A0 =A01
> > =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A099999
>
> > Will the value of interest always be 99999, or will it be the highest
> > value with a matching COLA_FK? =A0If 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. =A0I had figured out the
> =A0 > =A0 TABLEA.COLA=3DTABLEB.COLA_FK(+)
>
> but did not know I could do this:
>
> =A0> =A0 AND TABLEB.COLC(+)=3D99999;
>
> As a matter of fact you are right - how did you guess - in my cases,
> the interest of value would be max of whatever
> =A0is in TABLEB.COLC - if the row with 99999 does not exist, then the
> sql returns the fow with data in COLC=3D12345.
> =A0Also, 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 =3D 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=3DB.COLA_FK(+)
  AND B.DR(+)=3D1;

      COLA    COLA_FK       COLC
---------- ---------- ----------
         2          2     111111
         3          3         11
         1

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.