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: Query Help! (A Simple Filtering Problem)

Re: Query Help! (A Simple Filtering Problem)

From: M Hashim <mhashim_ca_at_passport.ca>
Date: Wed, 10 Jan 2001 23:18:20 -0500
Message-ID: <3A5D340C.C5E5DB3@passport.ca>

Try this:

SELECT id1,MAX(id2)
FROM tab1
WHERE id2 in(0,1) --since you do not care for id2=2 GROUP BY id1;

contrapositive 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/
Received on Wed Jan 10 2001 - 22:18:20 CST

Original text of this message

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