Finding Duplicate Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carroll - 30 Aug 2006 16:14 GMT

I'm looking for a way in SQL to find duplicate records in a single
table, that are the same based on 3 columns, regardless of what is in
the other columns in the duplicate records. I would like to keep both
records (or it could be more than 2 as well) where duplicate records
are found. Also, I am interested in selecting all columns from the
duplicate records.

Thanks,

Carroll Rinehart

Reply to this Message

Serge Rielau - 30 Aug 2006 16:40 GMT

> I'm looking for a way in SQL to find duplicate records in a single
> table, that are the same based on 3 columns, regardless of what is in
> the other columns in the duplicate records. I would like to keep both
> records (or it could be more than 2 as well) where duplicate records
> are found. Also, I am interested in selecting all columns from the
> duplicate records.

SELECT cnt, c1, c2, c3, c4, c5
(SELECT COUNT(1) OVER(PARTITION BY c1, c2, c3) AS cnt,
c1, c2, c3, c4, c5
FROM T) AS S
WHERE cnt > 1;

If you want to learn how to remove:
IOD Coneference
TLU-1288A SQL on Fire!
Oct 17 02:30 PM - 05:30 PM
Convention Center - 206A
seating is limited. ;-)

Or IDUG 2007 Europe in Vienna in 4 short weeks....

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Reply to this Message

Brian Tkatch - 30 Aug 2006 17:48 GMT

> I'm looking for a way in SQL to find duplicate records in a single
> table, that are the same based on 3 columns, regardless of what is in
[quoted text clipped - 6 lines]
>
> Carroll Rinehart

SELECT * FROM table
WHERE (Col1, Col2, Col3) IN
(
SELECT Col1, Col2, Col3 FROM table
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1
)

OR

SELECT * FROM table OUTER
WHERE EXISTS
(
SELECT Col1, Col2, Col3 FROM table INNER
WHERE INNER.Col1 = OUTER.Col1
AND INNER.Col2 = OUTER.Col2
AND INNER.Col3 = OUTER.Col3
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) > 1
)

B.

Reply to this Message