On May 11, 8:05=A0am, Charles Hooper <hooperc2...@[EMAIL PROTECTED]
> wrote:
> 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
> =A0 COLB,
> =A0 COLA_FK,
> =A0 COLC,
> =A0 DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
> FROM
> =A0 TABLEB;
>
> =A0 =A0 =A0 COLB =A0 =A0COLA_FK =A0 =A0 =A0 COLC =A0 =A0 =A0 =A0 DR
> ---------- ---------- ---------- ----------
> =A0 =A0 =A0 =A0 13 =A0 =A0 =A0 =A0 =A02 =A0 =A0 111111 =A0 =A0 =A0 =A0
=A0=
1
> =A0 =A0 =A0 =A0 12 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A099999 =A0 =A0 =A0 =A0
=
=A02
> =A0 =A0 =A0 =A0 11 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A012345 =A0 =A0 =A0 =A0
=
=A03
> =A0 =A0 =A0 =A0 13 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 11 =A0 =A0 =A0
=A0=
=A01
>
> We are only interested in the rows with DR =3D 1, so we need a way to
> eliminate the unnecessary rows. =A0If 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. =A0We can then alias the inline view (as B), and join it to
> TABLEA as before:
> SELECT
> =A0 TABLEA.COLA,
> =A0 B.COLA_FK,
> =A0 B.COLC
> FROM
> =A0 TABLEA,
> =A0 (SELECT
> =A0 =A0 COLB,
> =A0 =A0 COLA_FK,
> =A0 =A0 COLC,
> =A0 =A0 DENSE_RANK() OVER (PARTITION BY COLA_FK ORDER BY COLC DESC) DR
> =A0 FROM
> =A0 =A0 TABLEB) B
> WHERE
> =A0 TABLEA.COLA=3DB.COLA_FK(+)
> =A0 AND B.DR(+)=3D1;
>
> =A0 =A0 =A0 COLA =A0 =A0COLA_FK =A0 =A0 =A0 COLC
> ---------- ---------- ----------
> =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A02 =A0 =A0 111111
> =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 11
> =A0 =A0 =A0 =A0 =A01
>
> 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
|
