[RESOLVED] Delete dublicate rows

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

[RESOLVED] Delete dublicate rows

Author

Topic

christosds
Starting Member

10 Posts

Posted - 2012-07-27 : 06:11:59

Hello
i have a table like this

CCCORCODE|| MTRL || CODE || COMMENTS
===================================================================
1037 || 15597 || 441906091A || NULL
1038 || 15597 || 441906091B || NULL
1039 || 15597 || 443906087BB || NULL
1040 || 15597 || 443906087BD || NULL
1041 || 15597 || 443906091 || NULL
1042 || 15597 || 443906091A || NULL
1043 || 15597 || 443906091E || NULL
1044 || 15597 || 443906087BB || NULL
1045 || 15597 || 447906091A || NULL
1046 || 15597 || 443906087BB || NULL

Is it possible to delete all the double rows when MTRL & CODE are identical and keep only one row:

1039 15597 443906087BB NULL (keep only one row)
1044 15597 443906087BB NULL
1046 15597 443906087BB NULL

Any help will be greatly appreciated!?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 06:22:11

[code]
DELETE D
FROM
(
SELECT *, RN = row_number() over (partition by MTRL, CODE order by CCCORCODE)
FROM yourtable
) D
WHERE D.RN <> 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 06:24:32

You can use the row_number function to do this:

;WITH cte AS
(
	SELECT
		ROW_NUMBER() OVER (PARTITION BY MTRL,CODE ORDER BY (SELECT NULL)) AS RN
	FROM
		YourTable
)
DELETE FROM cte WHERE RN > 1;

The columns in the PARTITION clause are the columns which you want to delete duplicates of. If you have some ordering scheme that you can use from among the duplicates to decide which one to keep, instead of (SELECT NULL), insert the columns that you would use to order the duplicate rows. The first one in the ordering scheme will be kept.

christosds
Starting Member

10 Posts

Posted - 2012-07-27 : 07:21:53

Guys thanks for the prompt reply!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources