Finding Duplicate Records

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

re: Finding Duplicate Records

Carroll wrote: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/

re: Finding Duplicate Records

Carroll wrote: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.