On May 11, 8:05*am, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On May 11, 12:07*am, fergus <fergus_v...@yahoo.com> wrote:
>
>
>
>
>
> > On May 10, 11:21*am, Charles Hooper <hooperc2...@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.- Hide quoted text -
>
> - Show quoted text -


wow!
Thank you very much - that would be my introduction to analytical
functions in oracle :-)
Awesome!

- fergus