Truncating duplicate entries in a table

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

General SQL Server Forums

New to SQL Server Programming

Truncating duplicate entries in a table

Author

Topic

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-18 : 11:20:35

Hi,

I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it.

Can anyone provide me the syntax for the same?
I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX.

Please help me out...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-18 : 11:41:59

select col1, col2 , count(*)
from ISSR_TBL
group by col1,col2
having count(*) >1

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-18 : 11:56:15

To see all of the Duplicat details


SELECT *
  FROM ISSR_TBL o
WHERE EXISTS (
		  SELECT *
		    FROM ISSR_TBL i
		   WHERE i.Col1 = o.Col1
		     AND i.Col2 = o.Col2 
		GROUP BY col1,col2
		  HAVING COUNT(*) >1)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-18 : 12:05:11

Thanks Brett and Jim...This was really helpful....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources