OraFAQ Forum: SQL & PL/SQL » How to retrieve duplicate records from a table

Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve duplicate records from a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to retrieve duplicate records from a table [message #10638]

Fri, 06 February 2004 07:22

swetha
Messages: 10
Registered: November 2000

Junior Member

Hi,

I'm new to Oracle. Can anyone tell me how to find duplicate records from a table.

Given a table, e.g. a list of contact names and telephone numbers, how could you write a query to return a list of duplicate records in the table (same name, same phone number)?

Thanx

�

Report message to a moderator

Re: How to retrieve duplicate records from a table [message #10639 is a reply to message #10638]

Fri, 06 February 2004 07:47

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

SELECT name, phone
FROM   yourtable
GROUP BY name, phone HAVING COUNT(*) > 1;

This works by summarising the data and filtering the summary.

You can get the individual records (e.g. handy for deleting duplicates) with

SELECT *
FROM   yourtable
WHERE  ROWID IN
       ( SELECT LEAD(ROWID)
           OVER (PARTITION BY name, phone ORDER BY name, phone)
         FROM   yourtable );

Report message to a moderator

Re: How to retrieve duplicate records from a table [message #10640 is a reply to message #10639]

Fri, 06 February 2004 07:56

swetha
Messages: 10
Registered: November 2000

Junior Member

Thanx a lot.

Report message to a moderator

Previous Topic:	Foreign Key pointing to Multiple Tables
Next Topic:	Very very Urgent help needed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 13:13:03 CDT 2024