Re: Query Help! (A Simple Filtering Problem)

An other way can look like this

SELECT ID1, MAX(ID2) FROM T_TEST
WHERE DECODE(ID2,1,1,0,1) = 1
GROUP BY ID1 Chris

In article <93i7e5$56i$1_at_nnrp1.deja.com>,   David Fitzjarrell <oratune_at_aol.com> wrote:
> In our last gripping episode contrapositive <contrapositive_at_my-
> deja.com> wrote:
> > 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/
> >
>
> Try this, it should return the result set you specify:
>
> select i.id1, max(i.id2)
> from (select id1, id2
> from qtest
> where id2 = 0
> union
> select id1, id2
> from qtest
> where id2 = 1) i
> group by i.id1;
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 10 2001 - 12:13:04 CST