Solved

select one record of several

Posted on 2011-09-06
182 Views
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
Question by:DerCornet

LVL 6

Accepted Solution

markterry earned 500 total points
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'))
``````
0

LVL 42

Expert Comment

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

Author Closing Comment

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

Join & Write a Comment Already a member? Login.

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!