How to delete the duplicate records in the table ?

Question

Post reply

How to delete the duplicate records in the table ?

parthi-1705

SSCrazy Eights

Points: 9277
More actions
May 24, 2010 at 7:23 am

#235391

hi
How to delete the duplicate records in the below table without using Temp table or permanent table and/or SET Rowcount XXXXX or Cursor
Create table t1 (i int,b int)
Insert into t1
select 1,7
union all select 4,7
union all select 4,7
union all select 4,7
union all select 4,7
union all select 4,8
union all select 5,8
union all select 5,8
union all select 5,8
union all select 5,8
union all select 6,4
union all select 6,4
union all select 7,8
Select * from t1
select i,b,COUNT(*) from t1
group by i,b having COUNT(*)>1
order by i ,b
-- i need like
select 1,7
union select 4,7
union select 4,7
union select 4,7
union select 4,7
union select 4,8
union select 5,8
union select 5,8
union select 5,8
union select 5,8
union select 5,8
union select 6,4
union select 6,4
union select 7,8
Drop table t1
Thanks
Parthi
Thanks
Parthi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Try this for test purposes,

;with numbered as(SELECT rowno=row_number() over

(partition by i,b order by i),i,b from #t1)

select * from numbered

/*Giving the following results:

rowno i b

1 1 7

1 4 7

2 4 7

3 4 7

4 4 7

1 4 8

1 5 8

2 5 8

3 5 8

4 5 8

1 6 4

2 6 4

1 7 8 */

and if correct then replace the select * with :

delete * from numbered WHERE rowno > 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

This problem has been covered so many times that I'm surprised that Google doesn't give you a clue. Does it?

-- Gianluca Sartori

LutzM SSC Guru Points: 107049 More actions · Answer 3

Since your target table seems to be identical to what you have so far, the solutionis simple: do nothing 😀

To answer your question: based on the given talbe you can't. You would need to add an identity column to differentiate the rows. Based on that you can either use ROW_NUMBER() or a CTE to get the max(ID) per i,b combination and delete the rest using a EXCEPT statement.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]