Choose exactly one row for each employee using an unordered field as criteria

Question

I have a dataset that looks like this.

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 set the settings. I want the employee’s HOME phone number to be the same as employee 100 and 101. If the HOME number is not available, I want the WORK number (employee 102), and as a last resort I’ll take ANOTHER number as the employee 103. Actually, mine the 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 ​​shown in the example. Any suggestions? Thank.

Answer 1

, Server 2000 Coalesce? , , :

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 2

phone_types (Phone_Type TEXT (), INTEGER). Phone_Type ( HOME 1, WORK 2, OTHER 3 ..).

, "" Phone_Types Phone_Numbers (, Phone_Numbers_Ex).

Phone_Numbers_Ex MIN () emplID, , , :

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)

- SELECT EmplID, MIN(Priority) AS Priority FROM Phone_Numbers_Ex GROUP BY EmplID, Phone_Numbers_Ex EmplID, Priority.

Answer 3

, . phone_number_id, , , . , -.

- Phone_Types, , . , . , .

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)
            )
    )

LEFT JOIN NOT EXISTS, TOP, . -1 , phone_number_id.

, SQL 2005 SQL 2008, CTE ROWNUMBER() OVER (ORDER BY priority, phone_number, PARTITION BY employee_id) < - , , , , . , , ROWNUMBER() = 1.

Answer 4

As an alternative answer to gddc that uses queries in the Select clause, you can use left connections. You can get the best performance, but you have to 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'