Re: How To List Duplicate Records ??

try a

HAVING COUNT(*) > 1 clause

select cust_no
from customer
having count(*) > 1;

i think... ( no instance handy)

randy

Angelica Veron wrote:

> Hello Everyone,
>
> I have the following table called Customer....
>
> SQL> desc customer;
>
> Name Null? Type
> ------------------------------- -------- ----
> CUST_ID NOT NULL NUMBER(10)
> RELMGR_ID NUMBER(15)
> HH_HOUSEHOLD_ID NOT NULL NUMBER(10)
> CUST_HIST_ID NOT NULL NUMBER(15)
> CUST_NUM NOT NULL VARCHAR2(10)
> CUST_FIRST_NAME VARCHAR2(40)
> CUST_LAST_NAME VARCHAR2(40)
> CUST_REL_MGR_ASSIGN_DATE DATE
> CUST_RB_REL_MGR_FLAG CHAR(1)
> CUST_BIRTHDATE DATE
> CUSTOMER_ADD_RUN NOT NULL NUMBER
> CUSTOMER_UPD_RUN NOT NULL NUMBER
> CUST_CURRENT_FLAG CHAR(1)
> BEGIN_DATE DATE
>
> SQL> select count(*) from customer;
>
> COUNT(*)
> ---------
> 1311351
>
> This table was recently created, and I need to check for the
> existance of records with differing CUST_ID's but have the same
> CUST_NUM. Overall, I need to locate all records which have the same
> CUST_NUM, but different CUST_ID's , and the CUST_CURRENT_FLAG has to
> equal 'Y' (meaning they are current). Does anyone know which query will
> list such duplicate records for me ??... any help would be greatly
> appreciated, thank you for your time.
>
> Friendly Regards,
> Angelica Veron
> Faro_at_globalserve.net
Received on Tue Jul 06 1999 - 18:57:18 CDT