Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Remove Duplicate Records (Rows) from a Table

Jul 28, 2014
Introduction:

Here I will explain how to remove duplicate records from a table in SQL server or delete duplicate rows or records in sql server or removing duplicate records using sql server or sql server delete duplicate records or rows from table
Description:
In previous articles I explained about substring function in SQL server and Replace function in SQL Server and I explained how to delete duplicate records from datatable in asp.net. Now I will explain how to delete duplicate records from a datatable in SQL server. 

During work with one application I got requirement like get the unique records from datatable in sql server. Actually our datatable does not contain any primary key column because of that it contains duplicate records that would be like this

Actually above table does not contain any primary key column because of that same type of records exist.

Now I want to get duplicate records from datatable for that we need to write query like this

WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Position ORDER BY Name) As RowNumber,* FROM EmployeData
)
SELECT * FROM tempTable
Once we run above query we will get data like this

If you observe above table I added another column RowNumber this column is used to know which record contains duplicate values based on rows with RowNumber greater than 1.  

Now we want to get the records which contains unique value from datatable for that we need to write the query like this


WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Position ORDER BY Name) As RowNumber,* FROM EmployeData
)
DELETE FROM tempTable where RowNumber >1
SELECT * FROM EmployeData order by Id asc
Once we run above query all duplicate records will delete from our table and that would be like this

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

8 comments :

Unknown said...

really helpful..

Anonymous said...

Great Surya>>>>>>>>>>>>>>>>>>

Anonymous said...

Suya????????
HAHAHAHHAHAHA................
Surya Bhai Hai Aeto................

Anonymous said...

Are Bhai Ise Surya Mat Bolo Yeh To Asp.net KA BAP Hai.............

Anonymous said...

Kuch Bhi Ho Ham To Surya Bhai Hi Bolege>>>>>>>>>>>>>

Unknown said...

Can u please tell me to how to get cde from abc>bcd>cde>asp using substring and charindex.

Anonymous said...

kl

Unknown said...

Greate Solution Suresh

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.