asked on

select one record of several

I have a table of phone numbers with the following structure:

PersonID, PhoneType, PhoneNumber

Each person can have several phone numbers with different types, and not everybody has the same type.

I am looking for a view which gives me just one phone number, selected by a pre-established order of phonetype. Whenever the view has found this type, it should continue to the next person.

Example:
1000,HOME,1231231110
1000,WORK,1231231111
1000,CELL,1231231112
1001,WORK,1231231113
1002,CELL,1231231114
1002,MESS,1231231115
1003,FAM,1231231116
1004,HOME,1231231117
1004,FAM,1231231118

my selected order is: HOME - WORK - CELL

then the view should contain:

1000,1231231110
1001,1231231113
1002,1231231114
1004,1231231117

How would I write this select statement?

Thanks!

ASKER CERTIFIED SOLUTION

markterry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

Select PersonID, PhoneType, PhoneNumber
From
(
Select *, row_number() over(partition by PersonID
order by
case when PhoneType = 'Home' then 1
when PhoneType = 'Work' then 2
when PhoneType = 'Cell' then 3
else 4
end asc
) as precedence
from YourTable) as T1
where T1.precedence = 1

DerCornet

ASKER

The solution works fine, but you are right, it doesn't seem to be an elegant solution. I leave the elegant form for another day. I needed a solution now, and that's what it does. (It became a little more involved as I have to look for 7 different phone types ...) And I fear it is a rather slow solution.