Software Technology Tips

As I have mentioned in one of my previous tips that using ROW_NUMBER() we can get the serial number which is here.
 
Using the same ROW_NUMBER() function you can also easily delete the Duplicate Records present inside a table.

 
Lets say I have a Users Table which is having duplicate records. I need to delete the duplicate records which are having both the FirstName and LastName same.

 

 
Table: UsersList

 
FirstName       LastName       PhoneNumber       Address
------------    ------------     ----------------    -------------
Rashmita        Devi                987554437           NULL
Rashmita        Devi                446576578           NULL
Adwin            Ratzz              222222222           NULL
Charle            Hardwick         343534545           NULL
Adwin            Ratzz              576767688           NULL
Charle            Hardwick         877778777           NULL

 

 
So, with the help of ROW_NUMBER() all the duplicate records present in the above table can be removed easily.
The query will be composed this way:-

 
WITH TempUsers (FirstName,LastName, duplicateRecordCount)
AS
(
SELECT FirstName,LastName,
ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount
FROM dbo.Users
)
DELETE
FROM TempUsers
WHERE duplicateRecordCount > 1
GO

 
 
Instead of TempUsers you can give any name. Because this is used only for Temporary purpose.
 
After the execution of the above query, Users table will have the following records.

 
FirstName          LastName        PhoneNumber         Address
------------       ------------      ----------------      -------------
Rashmita           Devi                987554437              NULL
Adwin               Ratzz              222222222             NULL
Charle               Hardwick         877778777             NULL
 
 

 
Note: Row_Number() function is present in SQL Server 2005 and in later version(s).


Related Tags:

Database, SQL, SQL Server, T-SQL

Author: Rashmita Devi

Database

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

This site uses cookies. We respect your privacy.copyright (c) Mindfire Solutions 2007-2015. Login