Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Query help needed
"hedrew3" <peteg_at_garlic.com> wrote in message
news:aad10be0.0405101414.3e0f9a39_at_posting.google.com...
> I have a table X with columns contract_number and support_number. I am
> trying to query for the contract_numbers that have more than one
> DIFFERENT support_numbers associated with it.
>
> TIA
>
> Pete
Pete,
My suggestion would be:
SELECT
contract_number
FROM
x
GROUP BY
contract_number
HAVING
COUNT( DISTINCT support_number ) > 1
;
Your requirement, "...have more than one DIFFERENT support_numbers...",
translates into:
COUNT( DISTINCT support_number ) > 1
Because I need to process the table by grouping rows with the same contract_number together, I need to specify a GROUP BY clause. And because I only want certain groups, I need to specify a HAVING clause (not a WHERE clause).
First, I create some test data:
CREATE TABLE x AS
SELECT
TRUNC( ROWNUM / 10 ) + 1 AS contract_number, ROWNUM AS support_number FROM all_objects WHERE ROWNUM <= 21
UPDATE x SET support_number = 100 WHERE contract_number = 1;
This gives a set of test data that has the same support_number for contract_number = 1 and different support_number values for contract_number in { 2, 3 }. The result of the query is: CONTRACT_NUMBER
2 3
Douglas Hawthorne Received on Tue May 11 2004 - 09:16:33 CDT
![]() |
![]() |