Solved

Removing duplicates based on limited Columns

Posted on 2016-07-23
6
13 Views
Last Modified: 2016-10-04
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
Comment
Question by:sonic1234
6 Comments
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 334 total points
ID: 41726178
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
 
LVL 76

Expert Comment

by:arnold
ID: 41726179
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
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 334 total points
ID: 41726180
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
 

Author Comment

by:sonic1234
ID: 41726431
Thank you.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
ID: 41727445
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now