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.