?
Solved

Removing duplicates based on limited Columns

Posted on 2016-07-23
6
Medium Priority
?
22 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 1336 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 79

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 1336 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 664 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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