Xref: alice comp.databases.oracle.misc:19716
Path: alice!news-feed.fnsi.net!newsfeed1.earthlink.net!newshub.northeast.verio.net!btnet-peer!btnet-feed1!btnet!news-reader.bt.net!not-for-mail
From: "Preferred Customer" <@lineone.net>
Newsgroups: comp.databases.oracle.misc
References: <01bddbfd$5f3ec540$a049100a@pc743-sode.seurope.ikea.com>
Subject: Re: identical columns
Lines: 53
Organization: Dell Computer Corporation
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <UpBJ1.144$sJ3.449975@news-reader.bt.net>
Date: Wed, 09 Sep 1998 20:20:04 GMT
NNTP-Posting-Date: Wed, 09 Sep 1998 21:20:04 BST

Corinna

If duplicate rows is defined as, for example,
colA and colB are identical
then you could find the duplicate rows by
SELECT colA,colB
FROM tablename
GROUP BY colA,colB
HAVING COUNT(*) > 1;

If you want to information about other columns on the duplicate row,
eg. colC, then join the table to itself
SELECT colC
FROM tablename t1
WHERE t1.colA||t1.colB
IN

SELECT colA||colB
FROM tablename t2
GROUP BY colA||colB
HAVING COUNT(*) > 1);

and finally if you want to get rid of all the duplicate rows and leave only
a unique set, then

DELETE tablename
WHERE rowid IN
(SELECT t1.rowid
FROM tablename t1,
              tablename t2
WHERE t1.colA = t2.colA
       AND t1.colB = t2.colB
       AND t1.rowid > t2.rowid);

For a large table this statement will be much faster if you build an index
on the columns which define duplication, colA and colB in this example


Rupert




Corinna Becker wrote in message
<01bddbfd$5f3ec540$a049100a@pc743-sode.seurope.ikea.com>...
>Hello,
>I have the following problem: I have a table of which I know that it
>contains duplicate rows. I want to find all rows that are duplicate.
>How can I do that?
>Regards and thanks
>Corinna Becker