Re: Query Help! (A Simple Filtering Problem)

In our last gripping episode "Michel Cadot" <micadot_at_netcourrier.com> wrote:
> select id1, max(id2) from mytable
> where id2 in (0, 1)
> group by id1
> /
>
> --
> Have a nice day
> Michel
>
> "contrapositive" <contrapositive_at_my-deja.com> a écrit dans le message
 news:
> 93hp81$mv8$1_at_nnrp1.deja.com...
> > Hi. I need to know how I can query for rows based on the existence
 of
> > other rows. I've simplified the problem down to this. Suppose I
 have a
> > table with fields called ID1 and ID2, which together make up the
 key.
> > ID1 can be any number and ID2 can be 0, 1 or 2. I want all rows with
> > ID2 = 1. If there is no record with ID2 = 1, but there is a
> > corresponding record with ID2 = 0, then return it instead. Otherwise
> > ignore that record. I know that sounds complicated, but the idea is
> > pretty simple. If my table looks like this,
> >
> > ID1 ID2
> > ------ ------
> > 581 0
> > 581 1
> > 581 2
> > 582 0
> > 582 2
> > 583 2
> > 584 0
> > 584 1
> >
> > then my query should return
> >
> > ID1 ID2
> > ------ ------
> > 581 1
> > 582 0
> > 584 1
> >
> > Only one record of ID1 should be returned and its ID2 should be 1,
 or 0
> > if no record with ID2 = 1 exists.
> >
> > I can't use PL/SQL and I'm trying to avoid a subquery since the
 actual
> > queries I'm working with are already huge. That's what makes this
> > difficult. Does anyone see a way to do this using pure SQL? I would
> > sincerely appreciate any help at all.
> >
> > Thanks in advance.
> >
> > -jk
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Which proves there is more than one way to skin a cat...

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/