How to fetch duplicate records from a table

[RSub]

Hi,

Please let me know how to FETCH (not delete) duplicate records from a table.

Eg:

If a table has records like
Col1 Col2 Col3
1 a asdfs
2 b asgfged
3 c gsdgds
1 a asdfs

when the SQL query is executed, the result should be
Col1 Col2 Col3
1 a asdfs
1 a asdfs

[MAK]

create table tb1 (Col1 int,Col2 char(10), Col3 char(10))
insert into tb1 select 1, 'a', 'asdfs'
insert into tb1 select 2, 'a','asgfged'
insert into tb1 select 3, 'c g', 'gsdgds'
insert into tb1 select 1, 'a','asdfs'

--One way of doing this is
select a.col1,a.col2,a.col3 from tb1 a, (select col1,col2,col3 from tb1 group by col1,col2,col3 having count(*)>1) as b
where a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3

[RSub]

Hi,

Thanks a lot ...but I was wondering if there is any other way using ROWID..
Please let me know the alternate solution (using ROWID) also.

Thanks n regards
Sub

[MAK]

Not in SQL server 2000.
may be in next release.

[RSub]

you mean..if there are "n" columns in a table then we have to write "n" coditions in the where clause???The solution given is ok if the columns are <5 to 10..wht if there are more columns??
Please let me know if there is any other solution for this problem.

[skhanal]

Unfortunately you have to group by all columns to get duplicates.

Even if there was a ROWID as in Oracle, then ROWID will be different for duplicate rows, so you will not be able to use that.

This article has a generic script that works on any table so you don't have to type all columns.

http://www.databasejournal.com/featu...le.php/2235081

[umashankar1986]

hi
I am also facing same problem....
help me...
have u got any solution....