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/
Received on Wed Jan 10 2001 - 09:12:02 CST
