prioritize query results - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

prioritize query results

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-01-30 : 15:08:03

I’m not sure the best way to ask this, so bear with me.

Suppose you have this query…

SELECT Name, COALESCE(work_Phone, Cell_Phone, Home_Phone) AS 'Contact_Phone'
FROM Contact_Info

One contact phone number would be returned in the results. If a person has a work phone, the work phone number will be returned. If a person does not have a work phone and has a cell phone, the cell phone number will be returned. Finally, if a person does not have a work phone, does not have a cell phone, and has a home phone, the home phone number will be returned.

Now imagine that instead of having 3 separate columns of phone numbers (work, cell, and home), you only had one. In addition to the one, you have another column designating what type of phone number it is.

Emp_Id Phn_Cde Phn_Num
-------------------------------------
1357 Work (555)123-4567
1357 Home (555)987-6543
2468 Cell (555)333-4444
etc.

Finally, my question...

How would you return the same results in this case (one column of numbers) as in the query using coalesce (three columns of numbers). That is where one phone number is returned in order of priority (business, cell, and home).

Thanks.

Eric

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-30 : 15:15:58

one way would be to also create a table that defines the phoneTypes:

[phoneNumberType] (phoneTypeID tinyint, PhoneTypeDesc varchar(25), priority tinyint)


select ci.[name]
       ,pn.phoneNumber
from   Contact_Info ci
outer apply (
       select top 1 pn.phoneNumber
       from   PhoneNumbers pn
       inner join phonenumberType pnt on pnt.phonetypeid = pn.phonetypeid
       where  emp_id = ci.emp_id
       order by pnt.priority
       ) ph

Be One with the Optimizer
TG

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-30 : 15:24:31

This to me seems like a classic example where you should apply a normalization strategy on your table.

In fact this wiki article seems to suitably address your problem with a simple discussion on 1st normal form using telephone numbers. What you are addressing with your query is 'Repeating Groups Across Columns'
[url]http://en.wikipedia.org/wiki/First_normal_form[/url]

Check it out ! Its worth the read..

r&r

eevans
Starting Member

48 Posts

Posted - 2009-01-30 : 15:48:45

Sorry, I should have been more specific...

It actually does comply with 1NF. Employee names and phone numbers are located in separate tables with an id # as the primary key. It looks like the example 'A design that complies with 1NFA' in the Wikipedia Article.

Good read though. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:26:11

quote:
Originally posted by eevans

I’m not sure the best way to ask this, so bear with me.

Suppose you have this query…

SELECT Name, COALESCE(work_Phone, Cell_Phone, Home_Phone) AS 'Contact_Phone'
FROM Contact_Info

One contact phone number would be returned in the results. If a person has a work phone, the work phone number will be returned. If a person does not have a work phone and has a cell phone, the cell phone number will be returned. Finally, if a person does not have a work phone, does not have a cell phone, and has a home phone, the home phone number will be returned.

Now imagine that instead of having 3 separate columns of phone numbers (work, cell, and home), you only had one. In addition to the one, you have another column designating what type of phone number it is.

Emp_Id Phn_Cde Phn_Num
-------------------------------------
1357 Work (555)123-4567
1357 Home (555)987-6543
2468 Cell (555)333-4444
etc.

Finally, my question...

How would you return the same results in this case (one column of numbers) as in the query using coalesce (three columns of numbers). That is where one phone number is returned in order of priority (business, cell, and home).

Thanks.

Eric

something like


SELECT Emp_Id
COALESCE(MAX(CASE WHEN Phn_Cde='Work' THEN Phn_Num ELSE NULL END),
MAX(CASE WHEN Phn_Cde='Cell' THEN Phn_Num ELSE NULL END),
MAX(CASE WHEN Phn_Cde='Home' THEN Phn_Num ELSE NULL END))
FROM Table
GROUP BY Emp_Id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources