Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle SQL question..
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
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