selecting one row from duplicates

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

SQL Server 2005 Forums

Transact-SQL (2005)

selecting one row from duplicates

Author

Topic

macsterling
Yak Posting Veteran

56 Posts

Posted - 2009-02-05 : 15:19:27

I am creating (trying to) a table with address information. The source is a table that may have the address duplicated in several rows (three columns). I want to only create one copy of the address in the new table. How can I either isolate one row (don't care which) or not use the second, third etc. rows

WHAT I GOT TABLE
info1 info2 info3 CITY1 STATE1 ZIP1
info1 info2 info3 CITY2 STATE2 ZIP2
info1 info2 info3 CITY3 STATE3 ZIP3
info1 info2 info3 CITY1 STATE1 ZIP1
info1 info2 info3 CITY1 STATE1 ZIP1

WHAT I WANT TABLE
CITY1 STATE1 ZIP1
CITY2 STATE2 ZIP2
CITY3 STATE3 ZIP3

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 15:22:35

[code]Insert into newtable(columns)
Select distinct City,State,Zip from oldtable[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-02-05 : 15:27:11

My assumption is that the information has a identifier that you will want to keep (A address ID of some sort). Because of this Sodeep's method might not work for you due to you will not be able to bring over the identifier because it would cause the row to no longer to be "Distinct".

To avoid that you can use


Insert into NewTable(col1,col2,col3,col4)
Select info1,info2,info3,info4 --Your ID Col
from
(
Select Row_Number() over Partition by Info1,info2,info3 order by info4/*youridcol*/) as Row ID,info1,info2,info3,info4
from Mytable
) a
where a.RowID = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

macsterling
Yak Posting Veteran

56 Posts

Posted - 2009-02-05 : 15:44:11

Vinnie - you are right - I will try that
Thanks

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-05 : 23:00:23

quote:
Originally posted by Vinnie881

My assumption is that the information has a identifier that you will want to keep (A address ID of some sort). Because of this Sodeep's method might not work for you due to you will not be able to bring over the identifier because it would cause the row to no longer to be "Distinct".

To avoid that you can use

Insert into NewTable(col1,col2,col3,col4)
Select info1,info2,info3,info4 --Your ID Col
from
(
Select Row_Number() over (Partition by Info1,info2,info3 order by info4/*youridcol*/) as Row ID,info1,info2,info3,info4
from Mytable
) a
where a.RowID = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources