The query will be composed this way:-
WITH TempUsers (FirstName,LastName, duplicateRecordCount)
ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBYFirstName) AS duplicateRecordCount
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).