Find duplicate row in table in SQL
Posted by Rajesh on May 27, 2008
1. Create Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Test](
[Id] [int] NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
2. Insert Record
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (1,‘a’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (2,‘b’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (3,‘c’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (1,‘a’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (3,‘c’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (4,‘m’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (5,‘p’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (4,‘m’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (6,‘d’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (7,‘y’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (8,‘o’)
INSERT INTO [Test].[dbo].[tbl_Test]([Id] ,[Name]) VALUES (9,‘f’)
3. Find the duplicate record on the basis on Name field
1)
SELECT DISTINCT * FROM dbo.tbl_Test A
WHERE 1<(SELECT COUNT(*) FROM tbl_Test B
WHERE A.nAME=B.NAME)ORDER BY A.NAME
2)
SELECT ID, NAME FROM dbo.tbl_Test
GROUP BY NAME,ID
HAVING COUNT(*)>1
