Finding or eliminating dupes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Finding or eliminating dupes

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-04 : 09:29:08

Chinni writes "I need help from you
i want to find the duplicates in a table
like this
if a table has four fields

I want to find duplicates in four feilds

This is the table
col1 col2 col3 col4
ab cd dc xy
ab sc sf fg
sx dc ss sf
dx dc fc fe
ed re fg tu
dr te fg lu

I want the result to be like this

result
ab 2
dc 2
fg 2 or how many they are

i could find if it is like this

col1 col2 col3 col4
ab df fg sh
ab df fg sh

it would give out

ab 2

Thanks in advance,"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-04 : 11:43:26

select col1, col2, col3, col4, count (*) from tablename1 group by col1 col2 col3 col4 having count(*) > 1

will give you details of duplicate rows.

TO DELETE such items....there is an ARTICLE on this site with more info....(forum search...DELETE DUPLICATE)

to look for duplicate values in multiple individual columns....

select column2, count(*) from (
SELECT 'col1', col1, count(*) from tablename1
GROUP by col1
union
SELECT 'col2', col2, count(*) from tablename1
GROUP by col2
union
SELECT 'col3', col3, count(*) from tablename1
GROUP by col3
union
SELECT 'col4', col4, count(*) from tablename1
GROUP by col4)
group by column2
having count(*) > 1

this may get you part of the way....(i haven't tried it!!!)...be aware that the UNION clause eliminates duplicate values....therefore that's why I'm putting something unique into each "sub select" so that a result of "ab,2" from the 1st select won't prvent a result of "ab,2" also coming from the 2nd select....

the overall select (i think if it works) should aggregate the answers from the subselects....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources