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: SQL for query where result depends on result - Help!

Re: SQL for query where result depends on result - Help!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 21 Aug 1999 19:21:53 GMT
Message-ID: <37c3fbe5.22567510@newshost.us.oracle.com>


A copy of this was sent to "Adrian Bowen" <adrian_at_raptor.win-uk.net> (if that email address didn't require changing) On Sat, 21 Aug 1999 20:15:17 +0100, you wrote:

>As an SQL novice, I'm afraid I can't get my head around this - grateful for
>any help.
>
>I want to do a SELECT to implement a form of override - i.e. return an
>overriding value if present, else return the default. So the simplified
>result from my basic query looks like:
>
>TYPE MODE
>-------- ----------
>3 DEFAULT - want to get rid of this
>4 DEFAULT
>5 PRIVATE
>6 PRIVATE
>3 PRIVATE
>

select * from T
where not exists ( select null

                     from T t2
                    where t2.type = t.type
                      and t2.type = 'PRIVATE'
                      and t.type = 'DEFAULT' )

is one way.

>All I want to do is get rid of the first row, because there is a PRIVATE row
>with the same TYPE. I'm sure the answer will be blindingly obvious, should I
>stumble across it. Or should someone helpfully point me in the right
>direction. At the moment I can't even figure out how to do it on a straight
>table looking like the above!
>
>Ta,
>
>Adrian
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 21 1999 - 14:21:53 CDT

Original text of this message

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