Select exactly one row for each employee using unordered field as criteria

Question

I have a data set that looks like the following.

EMPLID  PHONE_TYPE  PHONE
------  ----------  --------
100     HOME        111-1111
100     WORK        222-2222
101     HOME        333-3333
102     WORK        444-4444
103     OTHER       555-5555

I want to select exactly one row for each employee using the PHONE_TYPE field to establish preferences. I want the HOME phone number if the employee has one as is the case for employee 100 and 101. If the HOME number is not present, I want the WORK number (employee 102), and as a last resort I'll take the OTHER number as with employee 103. In reality my table has about a dozen values for the PHONE_TYPE field, so I need to be able to extend any solution to include more than just the three values I've shown in the example. Any thoughts? Thanks.

Answer 1

You need to add a phone_types table (Phone_Type TEXT(Whatever), Priority INTEGER). In this table, list each Phone_Type value once and assign a priority to it (in your example, HOME would be 1, WORK 2, OTHER 3 and so on).

Then, create a view that joins the Priority column from Phone_Types to your Phone_Numbers table (imagine we call it Phone_Numbers_Ex).

Now, you have several options for how to get record from Phone_Numbers_Ex with the MIN(Priority) for a given emplID, of which probably the clearest is:

SELECT * FROM Phone_Numbers_Ex P1 WHERE NOT EXISTS
   (SELECT * FROM Phone_Numbers_Ex P2 WHERE P2.EmplID = P1.EmplID AND P2.Priority < P1.Priority)

Another way is to declare another view, or inner query, along the lines of SELECT EmplID, MIN(Priority) AS Priority FROM Phone_Numbers_Ex GROUP BY EmplID and then joining this back Phone_Numbers_Ex on both EmplID and Priority.

Answer 2

I forget, does Server 2000 support Coalesce? If it does, I think this will work:

Select Distinct EmplID, Coalesce(
  (Select Phone from Employees where emplid = e1.emplid and phone_type = 'HOME'),
  (Select Phone from Employees where emplid = e1.emplid and phone_type = 'WORK'),
  (Select Phone from Employees where emplid = e1.emplid and phone_type = 'OTHER')
) as Phone
From Employees e1

Answer 3

Your requirements may not be complete if an employee is allowed to have more than one phone number for a given phone type. I've added a phone_number_id just to make things unique and assumed that you would want the lowest id if the person has two phones of the same type. That's pretty arbitrary, but you can replace it with your own business logic.

I've also assumed some kind of a Phone_Types table that includes your priority for which phone number should be used. If you don't already have this table, you should probably add it. If nothing else, it lets you constrain the phone types with a foreign key.

SELECT
    PN1.employee_id,
    PN1.phone_type,
    PN1.phone_number
FROM
    Phone_Numbers PN1
INNER JOIN Phone_Types PT1 ON
    PT1.phone_type = PN1.phone_type
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            Phone_Numbers PN2
        INNER JOIN Phone_Types PT2 ON
            PT2.phone_type = PN2.phone_type AND
            (
                (PT2.priority < PT1.priority)
--OR (PT2.priority = PT1.priority AND PN2.phone_number_id > PN1.phone_number_id)
            )
    )

You could also implement this with a LEFT JOIN instead of the NOT EXISTS or you could use TOP if you were looking for the phone number for a single employee. Just do a TOP 1 ORDER BY priority, phone_number_id.

Finally, if you were to move up to SQL 2005 or SQL 2008, you could use a CTE with ROWNUMBER() OVER (ORDER BY priority, phone_number, PARTITION BY employee_id) <- I think my syntax may be slightly off with the parentheses on that, but hopefully it's clear enough. That would allow you to get the top one for all employees by checking that ROWNUMBER() = 1.

Answer 4

As an alternative g.d.d.c's answer that uses queries in the Select clause you could use left joins. You might get better perf, but you should test of course.

SELECT
    e1.iD,
    Coalesce(phoneHome.Phone,phoneWork.Phone,phoneOther) phone
FROm
    employees e1
    LEFT JOIN phone phoneHome 
    ON e1.emplId = phoneHome 
        and phone_type = 'HOME'
    LEFT JOIN phone phoneWork 
    ON e1.emplId = phoneWork 
        and phone_type = 'WORK'
    LEFT JOIN phone phoneWork 
    ON e1.emplId = phoneOTHER
        and phone_type = 'OTHER'