• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 25
  • Last Modified:

Removing duplicates based on limited Columns

Hello Experts,

I have a table where there are some duplicate entries.  I want to delete the additional duplicates, so only a single unique record remains.  However the thing I'm stuck on is that I only want to identify records as duplicated based on these fields;

[teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode]

USE [test]
GO
/****** Object:  Table [dbo].[tblSampleData]    Script Date: 07/24/2016 13:38:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSampleData](
	[teRecID] [float] NULL,
	[teAssessNum] [float] NULL,
	[teUnit] [float] NULL,
	[teHouse] [float] NULL,
	[teAlpha] [nvarchar](255) NULL,
	[teStreetName] [nvarchar](255) NULL,
	[teType] [nvarchar](255) NULL,
	[teSuburb] [nvarchar](255) NULL,
	[tePostCode] [float] NULL,
	[teLastVerified] [datetime] NULL,
	[teIssuableAddress] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (1, 1234, NULL, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (2, 1234, NULL, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (3, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (4, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (5, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (6, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (7, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (8, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (9, 1234, 10, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (10, 1234, 10, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (11, 1234, 11, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (12, 1234, 11, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (13, 1234, 12, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (14, 1234, 12, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (15, 1234, 13, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (16, 1234, 13, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (17, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (18, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (19, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (20, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (21, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (22, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (23, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (24, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (25, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (26, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (27, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (28, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (29, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (30, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (31, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (32, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (33, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (34, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (35, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (36, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (37, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (38, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (39, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (40, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (41, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (42, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (43, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (44, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (45, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (46, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (47, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (48, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (49, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (50, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (51, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (52, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (53, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
INSERT [dbo].[tblSampleData] ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) VALUES (54, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')

Open in new window

0
sonic1234
Asked:
sonic1234
3 Solutions
 
Olaf DoschkeSoftware DeveloperCommented:
Well, you group by the fields and then filter having count(*)>1:

Select Max(teRecID) as OnedoubleID 
FROM [dbo].[tblSampleData] 
GROUP BY [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode] 
HAVING COUNT(*)>1

Open in new window


This will chunk out a list of one (the latest) record of a group of records with equal [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], and [tePostCode].

So if you want to keep the record with min id this is a record to delete. You repeat until this query has an empty result.

If you want to keep the record with max id, query min(id) to find ids to delete.

Bye, Olaf.
0
 
arnoldCommented:
Run
Select teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], count([teUnit]) as "count"
from table where count([teUnit]) >1 group by teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode]
Order by count([teUnit]) desc

And see.
Usually adding an index using those columns as unique will have the server enforce the duplicate restriction.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Once youre sure this is what you want to delete you apply an outer DELETE:

DELETE FROM [dbo].[tblSampleData] WHERE teRecID IN (abovequery with MIN or MAX)

Open in new window


Bye, Olaf.
0
 
sonic1234Author Commented:
Thank you.
0
 
PortletPaulCommented:
You can use ROW_NUMBER() to good effect for this type of need.

declare  @tblSampleData TABLE (
	[teRecID] [float] NULL,
	[teAssessNum] [float] NULL,
	[teUnit] [float] NULL,
	[teHouse] [float] NULL,
	[teAlpha] [nvarchar](255) NULL,
	[teStreetName] [nvarchar](255) NULL,
	[teType] [nvarchar](255) NULL,
	[teSuburb] [nvarchar](255) NULL,
	[tePostCode] [float] NULL,
	[teLastVerified] [datetime] NULL,
	[teIssuableAddress] [nvarchar](255) NULL
) 

INSERT @tblSampleData ([teRecID], [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode], [teLastVerified], [teIssuableAddress]) 
VALUES 
   (1, 1234, NULL, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (2, 1234, NULL, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (3, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (4, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (5, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (6, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (7, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (8, 1234, 1, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (9, 1234, 10, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (10, 1234, 10, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (11, 1234, 11, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (12, 1234, 11, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (13, 1234, 12, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (14, 1234, 12, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (15, 1234, 13, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (16, 1234, 13, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (17, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (18, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (19, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (20, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (21, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (22, 1234, 2, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (23, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (24, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (25, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (26, 1234, 3, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (27, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (28, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (29, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (30, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (31, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (32, 1234, 4, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (33, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (34, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (35, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (36, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (37, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (38, 1234, 5, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (39, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (40, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (41, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (42, 1234, 6, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (43, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (44, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (45, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (46, 1234, 7, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (47, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (48, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (49, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (50, 1234, 8, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (51, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (52, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (53, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')
,  (54, 1234, 9, 1050, N'NULL', N'Oak', N'Road', N'SHELBYVILLE', 1234, CAST(0x0000A64D00DEF8DC AS DateTime), N'Yes')

Open in new window

Query using CTE and row_number...
;with CTE as (
        select
                *
              , row_number() over(partition by [teAssessNum], [teUnit], [teHouse], [teAlpha], [teStreetName], [teType], [teSuburb], [tePostCode] order by (select 1)) as rn
        from @tblSampleData
        )
delete 
from CTE
where rn > 1
;

select *
from @tblSampleData

Open in new window

Result:
teRecID teAssessNum teUnit teHouse teAlpha teStreetName teType teSuburb    tePostCode teLastVerified teIssuableAddress 
------- ----------- ------ ------- ------- ------------ ------ ----------- ---------- -------------- ----------------- 
1       1234        null   1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
3       1234        1      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
9       1234        10     1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
11      1234        11     1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
13      1234        12     1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
15      1234        13     1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
17      1234        2      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
23      1234        3      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
27      1234        4      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
33      1234        5      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
39      1234        6      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
43      1234        7      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
47      1234        8      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               
51      1234        9      1050    NULL    Oak          Road   SHELBYVILLE 1234       2016-07-24 13:31:49Yes               

(14 row(s) returned)

(94 row(s) affected)

Open in new window

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now