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.ThanksRaw DataID Addr1 City State Zip26 205 N Main ST Chicago IL 5214726 205 N Main ST Chicago IL 5214726 85 Park Ave Chicago IL 5214735 35 Main St Austin TX 7871535 976 Ponco St Dallas TX 7975735 9587 Mopac Austin TX 78715455 8741 Len Ln Dayton FL 74717455 518 Spring Dr Dayton FL 74717455 85 Park Ave Chicago IL 52147Desired Result ID Addr1 City State Zip26 205 N Main ST Chicago IL 5214726 85 Park Ave Chicago IL 5214735 35 Main St Austin TX 7871535 976 Ponco St Dallas TX 7975735 9587 Mopac Austin TX 78715455 8741 Len Ln Dayton FL 74717455 518 Spring Dr Dayton FL 74717455 85 Park Ave Chicago IL 52147CREATE TABLE #tmpTable( [ID] Int, [Addr1] [varchar](15) NULL, [City] [varchar](9) NULL, [State] [varchar](2) NULL, [Zip] [varchar](5) NULL,) GoInsert 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 #tmpTableSelect Distinct * from #tmpTableCheersMIK |
|
|
Robowski
Posting Yak Master
101 Posts |
Posted - 2014-03-07 : 10:08:34
|
First you need to look at why the ID column ha duplicatesAlter the table and ad a unique column so every row as a unique id:USE tempdb;GOIF 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,) GoInsert 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_dupeAS ( SELECT PK , ID , Addr1 , City , State , Zip , ROW_NUMBER() OVER (PARTITION BY Addr1 ORDER BY Addr1) as RwNm FROM #tmpTable )DELETE tFROM #tmpTable as tJOIN CTE_dupe as cteON t.pk = cte.pkAND cte.RwNm > 1Select * from #tmpTablequote: 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.ThanksRaw DataID Addr1 City State Zip26 205 N Main ST Chicago IL 5214726 205 N Main ST Chicago IL 5214726 85 Park Ave Chicago IL 5214735 35 Main St Austin TX 7871535 976 Ponco St Dallas TX 7975735 9587 Mopac Austin TX 78715455 8741 Len Ln Dayton FL 74717455 518 Spring Dr Dayton FL 74717455 85 Park Ave Chicago IL 52147Desired Result ID Addr1 City State Zip26 205 N Main ST Chicago IL 5214726 85 Park Ave Chicago IL 5214735 35 Main St Austin TX 7871535 976 Ponco St Dallas TX 7975735 9587 Mopac Austin TX 78715455 8741 Len Ln Dayton FL 74717455 518 Spring Dr Dayton FL 74717455 85 Park Ave Chicago IL 52147CREATE TABLE #tmpTable( [ID] Int, [Addr1] [varchar](15) NULL, [City] [varchar](9) NULL, [State] [varchar](2) NULL, [Zip] [varchar](5) NULL,) GoInsert 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
|
|
|
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 #tmpTableSelect Distinct * from #tmpTableCheersMIK
wont work:455 85 Park Ave Chicago IL 52147 26 85 Park Ave Chicago IL 52147 |
|
|
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 #tmpTableSelect Distinct * from #tmpTableCheersMIK
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 :) CheersMIK |
|
|
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 #tmpTableSelect Distinct * from #tmpTableCheersMIK
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 :) CheersMIK
My bad, thought he wanted to remove the duplicate address period, not only if they have the same Id.Your's would be better. |
|
|
|
|
|