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

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message >
> >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 )

Thomas's answer is correct.

Here is another solution, it looks tricky, but it takes less logical reads (in Oracle8).

select a.* from T a, T b

  where a.idnum=b.idnum(+)
    and a.status=2
    and a.status<b.status(+)
    and b.idnum is null;