Duplicate Rows

Question

Duplicate Rows

easy_goer

SSC Enthusiast

Points: 186
More actions
November 4, 2013 at 12:11 am

#299838

Hello. I have a table that contains several duplicate rows. I would like to remove the duplicates except for one of them. How can I accomplish this?
For instance, a small portion of my table looks like this..
Test1 3016482 56395 3
Test2 5489108 57921 2
Test1 3016482 56395 3
Test3 8079081 89302 1
Test4 8789080 24908 2
Test1 3016482 56395 3
There are three duplicates in here and I would like to eliminate two of them.
Thank you!

Viewing 8 posts - 1 through 7 (of 7 total)

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

You could use Row_Number to allocate a number to each row, partitioning by all of the columns. Then remove any row where the number is greater than 1.

Steve Hall
Linkedin
Blog Site

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 2

Or if your duplicate records keep coming via user input then you can create view with select distinct.

manickavasagam.g SSC Journeyman Points: 75 More actions · Answer 3

Select distinct * into #Temp_table from Original_table

Truncate table Original_table

insert into Original_table

select * from #Temp_table

Abu Dina SSChampion Points: 14155 More actions · Answer 4

CREATE TABLE dbo.SCC (

Col1 VARCHAr(10),

Col2 INT,

Col3 INT,

Col4 TINYINT)

INSERT INTO dbo.SCC (Col1, Col2, Col3, Col4)

VALUES ('Test1', 3016482, 56395, 3),

('Test2', 5489108, 57921, 2),

('Test1', 3016482, 56395, 3),

('Test3', 8079081, 89302, 1),

('Test4', 8789080, 24908, 2),

('Test1', 3016482, 56395, 3)

SELECT * from dbo.SCC

;with CTE_SCC AS

(select Col1, Col2, Col3, Col4,

ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3, Col4 ORDER BY Col1, Col2, Col3, Col4) AS Rec_No

from dbo.SCC)

SELECT * from CTE_SCC

where Rec_No = 1

DROP TABLE dbo.SCC

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

easy_goer SSC Enthusiast Points: 186 More actions · Answer 5

This is great. Thank you! Exactly what I needed. I do have a follow up questions however.

I have another, but similar table that looks like the following. I'm looking for a script that will purge the lines with the lowest numbers in the last column. So, just the row with the highest number in the last column to remain.

Test1 3016482 56395 1

Test1 3016482 56395 2

Test2 5489108 57921 2

Test2 5489108 57921 3

Test3 8079081 89302 1

Test3 8079081 89302 2

Test3 8079081 89302 3

Thanks again!

Abu Dina SSChampion Points: 14155 More actions · Answer 6

Something like this:

CREATE TABLE dbo.SCC (

Col1 VARCHAr(10),

Col2 INT,

Col3 INT,

Col4 TINYINT)

INSERT INTO dbo.SCC (Col1, Col2, Col3, Col4)

VALUES ('Test1', 3016482, 56395, 1), ('Test1', 3016482, 56395, 2),

('Test2', 5489108, 57921, 2),

('Test2', 5489108, 57921, 3),

('Test3', 8079081, 89302, 1),

('Test3', 8079081, 89302, 2),

('Test3', 8079081, 89302, 3)

SELECT * from dbo.SCC order by 1, 4

;with CTE_SCC AS

(select Col1, Col2, Col3, Col4,

ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 ORDER BY Col4 DESC) AS Rec_No

from dbo.SCC)

SELECT Col1, Col2, Col3, Col4 from CTE_SCC

where Rec_No = 1

DROP TABLE dbo.SCC

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

You can delete directly from the CTE:

DELETE from CTE_SCC

where Rec_No > 1

John