Re: Retrieve only recs that =1 and not

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 01 Jul 1999 12:03:06 GMT
Message-ID: <378158ba.54416366@newshost.us.oracle.com>

A copy of this was sent to zekmoe_at_aol.com (Zekmoe) (if that email address didn't require changing) On 01 Jul 1999 01:53:00 GMT, you wrote:

>Hello,
>I am trying to retrieve only the records in a table that are equal to a value,
>and don't have additional records with values greater. To clarify, the table is
>simplified like this:
>IDNum Status
> 123 1
> 123 2
> 123 3
> 124 1
> 124 2
> 125 1
> 125 2
> 125 3
>I need to get all the IDNum that are Status 2 but have not yet reached status
>3. Therefore I only need IDNum 124. Something like this
>(pseudo) Select IDNum from table where IDNum = 2 but not greater than 2;
>If I just specify < 3, I get 123 and 125 where the Status is 2.
>Any help greatly appreciated.
>Bob Maggio
>zekmoe_at_aol.com

select *
from T a
where status = 2
and NOT EXISTS ( select null

                      from T b
                     where b.idnum = a.idnum
                       and b.status > a.status )

--
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 Thu Jul 01 1999 - 07:03:06 CDT

Re: Retrieve only recs that =1 and not > 1?