Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle SQL question..

Re: oracle SQL question..

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/05/28
Message-ID: <3570bc62.2386591@192.86.155.100>#1/1

A copy of this was sent to gosar_at_EBB.Eng.Sun.COM (Bhavesh Gosar) (if that email address didn't require changing) On 27 May 1998 22:21:54 GMT, you wrote:

>Hi,
>
>I have table ....
>
>KC MKT PLT QTY
>---------- ---------- --- ----------
>100 M1 P1 1
>100 M1 P1 2
>100 M1 P1 3
>200 M2 P1 1
>300 M3 P3 1
>200 M2 P2 1
>100 M1 P2 1
>
>I want to select everything which is in PLT='P1' and
>only those records from other PLT's which have different
>combination of "KC, MKT and QTY fields"....
>
>The output should look as follows...
>

select *
  from T
 where plt = 'P1'
UNION ALL
select kc, mkt, plt, qty
  from T b
 where plt <> 'P1'
   and exists ( select NULL

                  from T c
                 where c.plt = b.plt
                   and ( c.kc <> b.kc OR c.mkt <> b.mkt OR c.qty <> b.qty ) )

would do it i think.... for this query to perform, we would expect PLT to be on the leading edge of some index so the subquery can do an index probe instead of full scanning.

>KC MKT PLT QTY
>---------- ---------- --- ----------
>100 M1 P1 1
>100 M1 P1 2
>100 M1 P1 3
>200 M2 P1 1
>300 M3 P3 1
>
>Is there any one-sql statement which can do this...??
>
>TIA,
>Bg
>
>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 28 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US