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 |
|
krylion
Starting Member
2 Posts |
 Posted - 2010-05-19 : 07:20:31
|
| Hi,I'm trying to find a solution to an issue where I want to remove some duplicate rows based on a value in that row.What I have is a table with a list of contacts and email addresses.Each contact can have more than one email address, and an email address has a priority number attached to it.I would like to select the email address with the highest priority for that contact.My table structure is like this:Person ID Person Name Priority Email*------* *----------* *----* *---* 1 John 1 john@abc.com 1 John 2 john@xzy.com 1 John 3 john@mno.com 2 Mary 2 mary@qwerty.com 2 Mary 3 mary@asd.com 3 Bob 1 bob@123qaz.com 3 Bob 2 bob@qaz.com 4 James 3 james@abc.comThe final table should be:Person ID Person Name Priority Email*------* *----------* *----* *---* 1 John 1 john@abc.com 2 Mary 2 mary@qwerty.com 3 Bob 1 bob@123qaz.com 4 James 3 james@abc.comI've tried many ways, but just can't find a way around selecting the the priority that is highest for a particular person.Any help would be much appreciated!Cheers,Dom |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-05-19 : 07:27:59
|
Hi -- are you using sql server 2005 or greater? if So ROW_NUMBER() is the way to go.Something LikeSELECT rnk.[Person ID] , rnk.[Person Name] , rnk.[Priority] , rnk.[Email]FROM ( SELECT [Person Id] AS [Person ID] , [Preson Name] AS [Person Name] , [Priority] AS [Priority] , [Email] AS [Email] , ROW_NUMBER() OVER (PARTITION BY [Person ID] ORDER BY [Priority] DESC) AS [Pos] FROM <THE TABLE> ) rnkWHERE rnk.[Pos] = 1 Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-05-19 : 07:47:44
|
If you don't like / have ROW_NUMBER() and the priorities will always be unique per employee (no multiple copies of priority 5 for example) then you can do:SELECT t.[Person ID] , t.[Person Name] , t.[Priority] , t.[Email]FROM <THE TABLE> AS t JOIN ( SELECT [Person Id] AS [Person ID] , MAX([Priority]) AS [Max Priority] FROM <THE TABLE> GROUP BY [Person Id] ) rnk ON rnk.[Person ID] = t.[Person ID] AND rnk.[Max Priority] = t.[Priority] Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
krylion
Starting Member
2 Posts |
Posted - 2010-05-19 : 23:15:52
|
| Charlie, you're a star!The row number solution worked a treat. Just had to change the DESC to ASC to get the priority right.Thanks for the help. ;)Dom |
|
|
|
|
|
|
|
|
Removing duplicate rows with a greater value