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