Find the duplicate records in the dataset

StrataScratch

#Google #Amazon

Find the duplicate records in the dataset.

Output the worker title, affected_from date, and the number of times the records appear in the dataset.

table name: title


Solution:

select worker_title,affected_from,count(*) as noOftimes
from title
group by worker_title,affected_from
having count(*) >1

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[title](
	[worker_ref_id] [int] NULL,
	[worker_title] [varchar](50) NULL,
	[affected_from] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (1, N'Manager', CAST(N'2016-02-20T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (2, N'Executive', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (8, N'Executive', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (5, N'Manager', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (4, N'Asst. Manager', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (7, N'Executive', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (6, N'Lead', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[title] ([worker_ref_id], [worker_title], [affected_from]) VALUES (3, N'Lead', CAST(N'2016-06-11T00:00:00.000' AS DateTime))
GO

Comments (0)
Questions List