Creating a complex WHERE clause

I need to get a phone number from a table.  I first want to see if there is a 'Work' number.  If not then look for a 'Business' number.  If not then look for a 'Direct' number.  If not then ' '.

Can I write this using one statement?

Table Phone:

fkContacts                   PhoneNO                         PhoneType
12155                         (213)555-1212               Direct
12123                         (213)566-3344               Home
12123                         (213)324-3213               Work
12199                          (310)555-1212               Business
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
WITH ctePhone AS
      SELECT pkPhone, fkContacts, PhoneNo, PhoneType,
            ROW_NUMBER() OVER(PARTITION BY fkContacts ORDER BY P2.Preference) AS RowNumber
      FROM Phone AS P
            SELECT pkPhone,
            CASE PhoneType
                  WHEN 'Work' THEN 1
                  WHEN 'Business' THEN 2
                  WHEN 'Direct' THEN 3
                  ELSE 9
            END AS Preference
            FROM Phone
            WHERE pkPhone = P.pkPhone
      ) AS P2
FROM ctePhone
WHERE RowNumber = 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
That works perfectly!!!!  Thank you once again for helping me so quickly.  

What is this type of query called?
Brian CroweDatabase AdministratorCommented:
The top clause is a common-table expression.  It is equivalent to having a correlated subquery like ...

FROM ...
WHERE somevalue IN (some other query)

I just find them easier to understand and maintain.  The CROSS APPLY is just used to minimize code reuse otherwise I would have to duplicate the entire CASE statement inside the ROW_NUMBER function.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.