Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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!
0
DerCornet
Asked:
DerCornet
1 Solution
 
markterryCommented:
Try this? may not be the prettiest solution, but if you want a view, that should work. I would probably do something with CTE and a procedure if I didnt know what phone types to expect though.
Select 
PersonID, PhoneType, PhoneNumber
from PhoneNumbers
Where PhoneType = 'Home' 
UNION ALL
Select 
PersonID, PhoneType, PhoneNumber
from PhoneNumbers
Where PhoneType = 'Work' 
and PersonID not in
(Select PersonID rom PhoneNumbers Where PhoneType IN ('Home'))
UNION ALL
Select 
PersonID, PhoneType, PhoneNumber
from PhoneNumbers
Where PhoneType = 'Cell'
and PersonID not in
(Select PersonID rom PhoneNumbers Where PhoneType IN ('Home', 'Work'))

Open in new window

0
 
dqmqCommented:
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

   
0
 
DerCornetAuthor Commented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now