delete duplicate records - 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 duplicate records

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-06 : 11:57:54

Have table orderrebate
ord_type,
ord_no,
item_no,
line_seq_no,
Cd_tp

If I have 3 rows that are identical I want to delete 2 and keep 1 of them.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-06 : 12:04:42

Guess this has been answered
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110167

ma.voice
Starting Member

12 Posts

Posted - 2008-09-06 : 13:45:16

Try following code:

CREATE TABLE #emp
(
empno int,
ename varchar(100),
deptno int
)
GO

INSERT INTO #emp values (1,'A',9)
INSERT INTO #emp values (1,'A',9)
INSERT INTO #emp values (1,'A',9)
INSERT INTO #emp values (1,'A',9)
GO

SELECT * FROM #emp
GO

DELETE T
FROM
(
SELECT TOP 3 * FROM #emp
) as T

SELECT * FROM #emp
GO

DROP TABLE #emp
GO

Hopefully this will solve your problem

Cheers.

Silent Voice
Bill Gates, MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-06 : 15:05:36

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARITION BY ord_type, ord_no, item_no, lin_seq_no ORDER BY cd_tp) AS RecID
) AS f
WHERE RecID > 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources