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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-09 : 08:24:24
|
| David Freeman writes "I have a table that has a listing of businesses. Some owners have more than one business. The common thread is the phone number. We assign the businesses to muliple people to call, but don't want multiple people calling the same owner.Therefore I need to develop a query that will identify the value in the assigned field (an integer) and for every record that has the same phone number (varchar 20) update the assigned value to that of the first occurrence:Assigned PrimaryPhone29 800-337-909034 800-337-909065 800-337-9090I need to make the assigned value of the second two records equal to 29Have thousands of these.SQL Server 2000, Windows Server 2003" |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2005-05-09 : 20:12:10
|
| QuestionDavid - how do you know which assigned is the one you want to go with - for example why 29, and why not 34 - if we just always go for the lowest assigned number (in this case 29) is that a problem?Also, do you want an (roughly) equal number of different phone numbers per assigned?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-05-09 : 23:38:59
|
quote:
Originally posted by AskSQLTeam
Assigned PrimaryPhone29 800-337-909034 800-337-909065 800-337-9090I need to make the assigned value of the second two records equal to 29Have thousands of these.SQL Server 2000, Windows Server 2003"
Assuming the Minimum Assigned Value will do...UPDATE M SET Assigned = MinAssigned FROM MyTable M INNER JOIN ( SELECT PrimaryPhone, MIN(Assigned) As MinAssigned FROM MyTable GROUP BY PrimaryPhone ) MiniMe ON MiniMe.PrimaryPhone = M.PrimaryPhone |
|
|
|
|
|
|
Modifying Records based on one duplicate field