TSQL – How to Find duplicate records in a SQL Server Table.

CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)
GO
INSERT INTO dbo.Customer
VALUES(1,’Rajesh’,’Yadav’,27)
,(1,’Rahul’,’sarode’,26)
,(2,’Raza’,’M’,32)
,(3,’Sukhjeet’,’Singh’,27)
,(4,’Sukhjeet’,’Singh’,28)
,(5,’Rahul’,’sarode’,26)

SELECT * FROM dbo.Customer

;WITH CTE
AS (
SELECT FirstName,
LastName,
Row_number()
OVER (
PARTITION BY FirstName, LastName
ORDER BY (
SELECT 1) ) AS Rn
FROM dbo.Customer)
SELECT *
FROM CTE
WHERE Rn > 1

DROP TABLE Customer

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s