Re: Identifying duplicate rows and querying results

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 10 Feb 2000 10:14:22 +0100
Message-ID: <87tvhe$2dtn$1@news5.isdnet.net>

May be something like that:

v734> -- cust_code with duplicates
v734> select cust_code from t group by cust_code having count(*) > 1;

CUST_CODE

5 rows selected.

v734> -- rows with duplicate cust_code and different cust_id v734> select t.*
2 from t,

  3       (select t.cust_code, t.cust_id
  4        from t,
  5             (select cust_code from t
  6              group by cust_code having count(*) > 1) b
  7        where t.cust_code = b.cust_code
  8        group by t.cust_code, t.cust_id having count(*) = 1) a

9 where t.cust_code = a.cust_code
10 and t.cust_id = a.cust_id
11 /

CUST_CODE CUST_ID AGE
---------- ---------- ---

        16        117 AIR
        16        123 AIR
        24        155 ARM
        24        198 ARM
        25        133 NAV
        25        192 AIR

6 rows selected.

Hope you have not to many rows for explain plan is: SELECT STATEMENT
MERGE JOIN
SORT JOIN

      VIEW
        FILTER
          SORT GROUP BY
            MERGE JOIN
              SORT JOIN
                VIEW
                  FILTER
                    SORT GROUP BY
                      TABLE ACCESS FULL T
              SORT JOIN
                TABLE ACCESS FULL T
    SORT JOIN
      TABLE ACCESS FULL T

--
Have a nice day
Michel

<milesr_at_my-deja.com> a écrit dans le message : 87s6pd$jrb$1_at_nnrp1.deja.com...
> Hi all,
>
> I'm trying to develop the PL/SQL code that can list only rows that have
> a repeating cust_code, then out of the results list rows that have the
> same cust_code but different cust_id. For example:
>
> CUST_CODE CUST_ID AGENCY
> 12 103 NAV
> 12 103 NAV
> 13 108 NAV
> 14 111 MAR
> 15 114 AIR
> 16 123 AIR
> 16 117 AIR
> 16 192 ARM
> 16 192 AIR
> 18 160 MAR
> 19 159 ARM
> 20 112 NAV
> 21 181 MAR
> 22 172 MAR
> 23 182 MAR
> 24 104 ARM
> 24 104 ARM
> 24 198 ARM
> 24 155 ARM
> 25 133 NAV
> 25 192 AIR
> 26 140 NAV
> 27 165 AIR
> 27 165 AIR
>
> I know that CUST_CODEs' 12, 16, 24, 25, 27 all have duplicates, but I'm
> trying to produce only the rows that also have a different CUST_ID.
>
>
> The desired output should look like this:
>
> CUST_CODE CUST_ID AGENCY
> 16 123 AIR
> 16 117 AIR
> 24 198 ARM
> 24 155 ARM
> 25 133 NAV
> 25 192 AIR
>
>
> Any help would be appreciated.
>
>
> Ronald
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Feb 10 2000 - 03:14:22 CST