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)
SELECT FirstName,LastName,
ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount
FROM dbo.Users
FROM TempUsers
WHERE duplicateRecordCount > 1

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


Let us Connect!

Awards and Achievements

Red Herringcolor DeloitteFast50 DB ZInnov1   Nascome  DB Stpi

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