Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Remove duplicate addresses for each IDs

Author  Topic 

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2014-03-07 : 09:02:00
Below is a raw data with SQL script to create the temp table. This table is a scale down version of what I am working with. I am taking the addresses from several different sources and putting them in a temp table. Now, I need to remove the duplicate addresses for each ID. Duplicate addresses are okay for different IDs.

Thanks


Raw Data
ID Addr1 City State Zip
26 205 N Main ST Chicago IL 52147
26 205 N Main ST Chicago IL 52147
26 85 Park Ave Chicago IL 52147
35 35 Main St Austin TX 78715
35 976 Ponco St Dallas TX 79757
35 9587 Mopac Austin TX 78715
455 8741 Len Ln Dayton FL 74717
455 518 Spring Dr Dayton FL 74717
455 85 Park Ave Chicago IL 52147

Desired Result
ID Addr1 City State Zip
26 205 N Main ST Chicago IL 52147
26 85 Park Ave Chicago IL 52147
35 35 Main St Austin TX 78715
35 976 Ponco St Dallas TX 79757
35 9587 Mopac Austin TX 78715
455 8741 Len Ln Dayton FL 74717
455 518 Spring Dr Dayton FL 74717
455 85 Park Ave Chicago IL 52147

CREATE TABLE #tmpTable(
[ID] Int,
[Addr1] [varchar](15) NULL,
[City] [varchar](9) NULL,
[State] [varchar](2) NULL,
[Zip] [varchar](5) NULL,
)
Go

Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '205 N Main ST', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '205 N Main ST', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '85 Park Ave', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '35 Main St', 'Austin', 'TX', '78715');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '976 Ponco St', 'Dallas', 'TX', '79757');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '9587 Mopac', 'Austin', 'TX', '78715');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '8741 Len Ln', 'Dayton', 'FL', '74717');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '518 Spring Dr', 'Dayton', 'FL', '74717');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '85 Park Ave', 'Chicago', 'IL', '52147');

Select * from #tmpTable


MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-07 : 09:56:03
Use "Distinct"

e.g.
Select * from #tmpTable
Select Distinct * from #tmpTable

Cheers
MIK
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 2014-03-07 : 10:08:34
First you need to look at why the ID column ha duplicates

Alter the table and ad a unique column so every row as a unique id:

USE tempdb;
GO
IF OBJECT_ID ('#tmpTable' ,'U') IS NOT NULL
DROP TABLE #tmpTable;

CREATE TABLE #tmpTable
(
[PK] int identity(1,1) NOT NULL,
[ID] Int,
[Addr1] [varchar](15) NULL,
[City] [varchar](9) NULL,
[State] [varchar](2) NULL,
[Zip] [varchar](5) NULL,
)
Go

Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '205 N Main ST', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '205 N Main ST', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '85 Park Ave', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '35 Main St', 'Austin', 'TX', '78715');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '976 Ponco St', 'Dallas', 'TX', '79757');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '9587 Mopac', 'Austin', 'TX', '78715');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '8741 Len Ln', 'Dayton', 'FL', '74717');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '518 Spring Dr', 'Dayton', 'FL', '74717');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '85 Park Ave', 'Chicago', 'IL', '52147');

Select * from #tmpTable

;WITH CTE_dupe
AS
(
SELECT PK ,
ID ,
Addr1 ,
City ,
State ,
Zip ,
ROW_NUMBER() OVER (PARTITION BY Addr1 ORDER BY Addr1) as RwNm
FROM #tmpTable
)

DELETE t
FROM #tmpTable as t
JOIN CTE_dupe as cte
ON t.pk = cte.pk
AND cte.RwNm > 1

Select * from #tmpTable

quote:
Originally posted by ugh3012

Below is a raw data with SQL script to create the temp table. This table is a scale down version of what I am working with. I am taking the addresses from several different sources and putting them in a temp table. Now, I need to remove the duplicate addresses for each ID. Duplicate addresses are okay for different IDs.

Thanks


Raw Data
ID Addr1 City State Zip
26 205 N Main ST Chicago IL 52147
26 205 N Main ST Chicago IL 52147
26 85 Park Ave Chicago IL 52147
35 35 Main St Austin TX 78715
35 976 Ponco St Dallas TX 79757
35 9587 Mopac Austin TX 78715
455 8741 Len Ln Dayton FL 74717
455 518 Spring Dr Dayton FL 74717
455 85 Park Ave Chicago IL 52147

Desired Result
ID Addr1 City State Zip
26 205 N Main ST Chicago IL 52147
26 85 Park Ave Chicago IL 52147
35 35 Main St Austin TX 78715
35 976 Ponco St Dallas TX 79757
35 9587 Mopac Austin TX 78715
455 8741 Len Ln Dayton FL 74717
455 518 Spring Dr Dayton FL 74717
455 85 Park Ave Chicago IL 52147

CREATE TABLE #tmpTable(
[ID] Int,
[Addr1] [varchar](15) NULL,
[City] [varchar](9) NULL,
[State] [varchar](2) NULL,
[Zip] [varchar](5) NULL,
)
Go

Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '205 N Main ST', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '205 N Main ST', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (26, '85 Park Ave', 'Chicago', 'IL', '52147');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '35 Main St', 'Austin', 'TX', '78715');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '976 Ponco St', 'Dallas', 'TX', '79757');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (35, '9587 Mopac', 'Austin', 'TX', '78715');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '8741 Len Ln', 'Dayton', 'FL', '74717');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '518 Spring Dr', 'Dayton', 'FL', '74717');
Insert into #tmpTable (ID, Addr1, City, State, Zip)
Values (455, '85 Park Ave', 'Chicago', 'IL', '52147');

Select * from #tmpTable




Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 2014-03-07 : 10:11:07
quote:
Originally posted by MIK_2008

Use "Distinct"

e.g.
Select * from #tmpTable
Select Distinct * from #tmpTable

Cheers
MIK




wont work:

455 85 Park Ave Chicago IL 52147
26 85 Park Ave Chicago IL 52147
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-07 : 10:42:29
quote:
Originally posted by Robowski

quote:
Originally posted by MIK_2008

Use "Distinct"

e.g.
Select * from #tmpTable
Select Distinct * from #tmpTable

Cheers
MIK




wont work:

455 85 Park Ave Chicago IL 52147
26 85 Park Ave Chicago IL 52147



I've just provided a query which will extract the desired output

I've not checked your solution but if it is doing same then even better ... by the way you can check if the output of your select statement is same as requested in the desired output :)

Cheers
MIK
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 2014-03-07 : 11:03:31
quote:
Originally posted by MIK_2008

quote:
Originally posted by Robowski

quote:
Originally posted by MIK_2008

Use "Distinct"

e.g.
Select * from #tmpTable
Select Distinct * from #tmpTable

Cheers
MIK




wont work:

455 85 Park Ave Chicago IL 52147
26 85 Park Ave Chicago IL 52147



I've just provided a query which will extract the desired output

I've not checked your solution but if it is doing same then even better ... by the way you can check if the output of your select statement is same as requested in the desired output :)

Cheers
MIK



My bad, thought he wanted to remove the duplicate address period, not only if they have the same Id.

Your's would be better.
Go to Top of Page
   

- Advertisement -