Solved

Removing duplicates based on limited Columns

Posted on 2016-07-23
6
19 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 77

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Syntax for query to update table 2 29
Currency in SQL? 2 32
Requesting help with creating an SQL query with 2 tables 6 27
MS SQL Conditional WHERE clause 3 15
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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