SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Remove duplicate addresses for each IDs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

59 Posts

Posted - 03/07/2014 :  09:02:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2014 :  09:56:03  Show Profile  Reply with Quote
Use "Distinct"

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

Cheers
MIK

Edited by - MIK_2008 on 03/07/2014 09:56:25
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 03/07/2014 :  10:08:34  Show Profile  Reply with Quote
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 - 03/07/2014 :  10:11:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2014 :  10:42:29  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 03/07/2014 10:50:43
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 03/07/2014 :  11:03:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000