Re: How To List Duplicate Records ??

Randy DeWoolfson <randy_at_euclidsys.com> writes:

> try a
>
> HAVING COUNT(*) > 1
>
> clause
>
> select cust_no
> from customer
> having count(*) > 1;
>
> i think... ( no instance handy)

HAVING needs a group by clause. But that won't get the rest of the record, just the cust_no for which duplicates exist. You could cook up something like

SELECT * FROM customer
WHERE cut_no IN (

	SELECT cust_no 
	FROM customer 
	GROUP BY cust_no
	HAVING count(*) > 1)

Or something like:

SELECT *
  FROM customer
 WHERE EXISTS (

	SELECT cust_no
	  FROM customer a
	 WHERE a.rowid   <> customer.rowid 
	   AND a.cust_no =  customer.rowid
	)

should work and would even be relatively efficient if cust_no has an index on it. If it doesn't then this is incredibly inefficient, create the index before you run this.

greg Received on Tue Jul 06 1999 - 22:27:31 CDT