Delete Similar rows - SQL Server Forums

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

Delete Similar rows

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-11-02 : 03:54:41

Hi Friends

I am having a table where i need to delete the similar records

My Table

Id Name Flag
1 Kotti 0
2 Prasad 1
3 Sam 0
1 Kotti 0
3 Sam 0

When i execute some query ,i need the 1st row and last row to be deleted since both rows are duplicate rows.(Details of both rows already exists)

Is there a way to delete like this?.If ,please help me to solve this problem.

Thanks in Advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-02 : 04:05:29

delete s from
(select row_number()over(partition by id,name,flag order by id) as rid , * from mytable)s where rid > 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-02 : 04:06:19

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

Pradip
Starting Member

32 Posts

Posted - 2009-11-02 : 04:22:35

Id Name Flag
1 Kotti 0
2 Prasad 1
3 Sam 0
1 Kotti 0
3 Sam 0

I think your question is about 1st and second last row in the table which is duplicate? Is It so?

pradipjain

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-02 : 04:23:43

depends on SQL version you are working if 2005 & above then bklr solution works well if in 2000 then you have to take it in temporary table and then delete.
So which version???

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Kotti
Posting Yak Master

129 Posts

Posted - 2009-11-02 : 06:36:21

Hello Friends,

Thanks for your replies,

I got the answer from the below sample code.

CREATE TABLE Ttemp(name VARCHAR(50) NOT NULL PRIMARY KEY);
GO
INSERT INTO Ttemp
SELECT DISTINCT * FROM T;
GO
DROP TABLE T;
GO
EXEC sp_rename 'Ttemp', 'T';
GO
SELECT * FROM T;

Thanks For all your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-02 : 07:20:25

quote:
Originally posted by Kotti

Hello Friends,

Thanks for your replies,

I got the answer from the below sample code.

CREATE TABLE Ttemp(name VARCHAR(50) NOT NULL PRIMARY KEY);
GO
INSERT INTO Ttemp
SELECT DISTINCT * FROM T;
GO
DROP TABLE T;
GO
EXEC sp_rename 'Ttemp', 'T';
GO
SELECT * FROM T;

Thanks For all your help.

Have you read my first reply?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources