Wednesday, April 20, 2016

Official Blog is Launched

Dotnet Mob Official Blog is Launched.
Please click here.

Saturday, April 25, 2015

Delete Duplicate Rows in Sql Server

Please find Original post from here


In this article, i would like to share a tip for deleting duplicate rows from sql server table.

For that we will be using a student table created from following query

CREATE TABLE tbl_Student
(
rollNumber INT,
studentName VARCHAR(MAX), 
address VARCHAR(MAX) 
)
after inserting for 4 rows into the table with one duplicate row.
image showing tbl_student
tbl_Student

following query will be helpful to know is there any duplicate rows based on rollNumber,

SELECT DISTINCT rollnumber,COUNT(*) AS [Number of Duplcates]
FROM tbl_Student
GROUP BY rollNumber
Query result will look like this
above query result

and shows that rollNumber with '1' is repeated twice

Now i am going to delete this duplicate rows and this can be done in two method using CTE

Please find Original post from here

Method 1 - by keeping original


In this method all duplicate rows will be deleted by preserving original copy and my query looks like this

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY rollNumber ORDER BY rollNumber) AS RN
FROM tbl_Student
)

DELETE FROM CTE WHERE RN<>1
after deletion table will be as shown below
method1 query result
after deletion

Please find Original post from here

Method 2 - without keeping original


Here we will delete all repeated rows including original copy and here is the query


WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY rollNumber)
FROM tbl_Student)

DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
resulting table will be like this
method2 query result
after deletion


-- Done Folks--
»Keep Sharing and Learning   «

Video Tutorial