By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

Finding Duplicate Records

P: n/a
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

Aug 30 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Carroll wrote:
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....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 30 '06 #2

P: n/a
Carroll wrote:
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
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.

Aug 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.