removing duplicate values and keeping original

Question

Post reply

removing duplicate values and keeping original

hemalchalishazar

SSC Veteran

Points: 285
More actions
April 8, 2010 at 12:32 pm

#217546

Hi,
I have around 5200 rows in 1 table which has same row displayed twice or thrice... I want to remove duplicate rows and want to keep original row (only 1 copy) and want to add this whole table into some other table.... please suggest me the query
thanks,
hemal

Viewing 6 posts - 1 through 5 (of 5 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

insert into destinationtable [Fields...]

SELECT DISTINCT [Fields...]

from sourcetable

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

hemalchalishazar SSC Veteran Points: 285 More actions · Answer 2

i tried that but it is not working....I have written code which is displayed below...

DELETE FROM dummy1

WHERE M_ItemID IN

(SELECT M_ItemID FROM dummy1

GROUP BY M_ItemID HAVING COUNT(M_ItemID) > 1)

but this is deleting all the copies of that row...i just want to delete additional duplicated rows and want to keep one original copy of the row...

Thanks,

Hemal

Garadin One Orange Chip Points: 29613 More actions · Answer 3

You've tried... the query I wrote? Or the one you wrote below that is totally different?

If the goal is to eventually to get rid of the table, then don't worry about cleaning it first, just grab one copy of each row and insert it into the other table(my query). If the goal is really to clean the table, then you can use ROW_NUMBER() for this.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

hemalchalishazar SSC Veteran Points: 285 More actions · Answer 4

i tried the same way before but it does not go in... i am not going to delete the table eventually as it is critical to keep that table without any duplicate rows....

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Mate here is one way of doing it...

i recommend u reading thro the post by Jeff Moden on how efficiently u can do it

http://www.sqlservercentral.com/Forums/Topic899000-338-1.aspx#bm900104

-- Create the table

if object_id('Duplicates') is not null

drop table Duplicates

Create table Duplicates

(

c1 int

)

-- insert some sample records

declare @i int set @i = 1

while (@i <= 5 )

begin

insert into Duplicates

select 1

union all

select 2

union all

select 3

set @i = @i + 1

end

select * From Duplicates order by c1

-- delete script

;WITH CTE (Rank_of_Duplicates , c1)

as

(

select row_number() over(partition by c1 order by c1 desc) as Rank_of_Duplicates, c1 from Duplicates

)

DELETE FROM CTE

WHERE

Rank_of_Duplicates not in

(

Select max(Rank_of_Duplicates) from cte

group by c1

)

select * From Duplicates order by c1