Finding the duplicates recordas

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

Finding the duplicates recordas

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-01-14 : 03:41:22

Dear All,

I need to find the duplicates on a table only for particular column.

table structure
ID int
date_modified smalldatetime
product_id int WITH NULL
prod_catogory Varchar(10)
comments varchar(100)
auditcomment varchar(1000)--default of currentuser with getdate()

Values
200 |2011:01:12:10:12:30 |NULL|fresh| addedusernamewithtimestamp-before|
200 |2011:01:12:10:13:30|NULL|fresh| addedusernamewithtimestamp-after|
200 |2011:01:11:01:01:00 |1 |fresh| addeusernamewithtimestamp|
200 |2011:01:01:00:00:00 |NULL|old | addedusernamewithtimestamp|

Here i need to find out the duplicates based on column ID and comment.
If the both ID and comment column are same i need to find those records and i need to delete after that.

How do we write query for this

Thanks in adavance

Thanks,
Gangadhara MS
SQL Developer and DBA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-14 : 03:56:38

select id,comment from table
group by id,column
having count(*)>1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 05:43:00

you mean delete all or delete duplicates and retain single occurance?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-01-16 : 23:07:30

I mean to say that from the original post
For the same ID and same timestamp(considering millisecond) for different comment i need to fetch and need to delete.

From the above sample data i need to get
Result set of

200 |2011:01:12:10:13:30|NULL|fresh| addedusernamewithtimestamp-after|
200 |2011:01:11:01:01:00 |1 |fresh| addeusernamewithtimestamp|
200 |2011:01:01:00:00:00 |NULL|old | addedusernamewithtimestamp|

If you observer first record is removed (id duplicate i need to retain any one record)

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:21:05

are you looking at fuzzy matches? all the four records above have slightly different comments then how you managed to group them to one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources