SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 [RESOLVED] Delete dublicate rows		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

christosds
Starting Member

Greece
10 Posts
Posted - 07/27/2012 :  06:11:59  Show Profile  Reply with Quote
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!?
Edited by - christosds on 07/27/2012 07:44:03

khtan
In (Som, Ni, Yak)

Singapore
16769 Posts
Posted - 07/27/2012 :  06:22:11  Show Profile  Reply with Quote 

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


KH
Time is always against us
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/27/2012 :  06:24:32  Show Profile  Reply with Quote
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.
Go to Top of Page

christosds
Starting Member

Greece
10 Posts
Posted - 07/27/2012 :  07:21:53  Show Profile  Reply with Quote
Guys thanks for the prompt reply!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000