Solved

Removing duplicates based on limited Columns

Posted on 2016-07-23
6
20 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 78

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 6 52
Not listening to where 1 42
TSQL remove duplicates from different columns 14 52
store vs query adhoc - no show rows 4 37
In this article I will describe the Copy Database Wizard 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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