Order SQL Using A Given, Distinct List

Say you have a requirement of updating the preferred phone number using a given, ad hoc, ordered list, where #1 in the list was the preferred number, if it exists, followed by #2 in the list and so on. Basically order by X, Y, Z.

This SQL will fit the bill: It's much easier to read the SELECT version first, as we can use the WITH clause:

with x_order as
      (         select 1 as ord, 'HOME' as phone_type from dual
      union all select 2 as ord, 'MOB'  as phone_type from dual
      union all select 3 as ord, 'WORK' as phone_type from dual
      union all select 4 as ord, 'SWK'  as phone_type from dual
      union all select 5 as ord, 'OTR'  as phone_type from dual
      union all select 6 as ord, 'FAX'  as phone_type from dual)

select * from ps_personal_phone ph
where emplid = '12345678'
and ph.phone_type =
      (select a.phone_type
      from
        ps_personal_phone a
      , x_order b
      where a.emplid = ph.emplid
      and b.phone_type = a.phone_type
      and b.ord =
            (select min(b2.ord)
            from
              ps_personal_phone a2
            , x_order b2
            where a2.emplid = a.emplid
            and b2.phone_type = a2.phone_type))
;

And the UPDATE version:

update ps_personal_phone ph
set ph.pref_phone_flag = 'Y'
where emplid = '12345678'
and ph.phone_type =
      (select a.phone_type
      from
        ps_personal_phone a
      ,
            (         select 1 as ord, 'HOME' as phone_type from dual
            union all select 2 as ord, 'MOB' as phone_type from dual
            union all select 3 as ord, 'WORK' as phone_type from dual
            union all select 4 as ord, 'SWK' as phone_type from dual
            union all select 5 as ord, 'OTR' as phone_type from dual
            union all select 6 as ord, 'FAX' as phone_type from dual) b
      where a.emplid = ph.emplid
      and b.phone_type = a.phone_type
      and b.ord =
            (select min(b2.ord)
            from
              ps_personal_phone a2
            ,
                   (         select 1 as ord, 'HOME' as phone_type from dual
                   union all select 2 as ord, 'MOB' as phone_type from dual
                   union all select 3 as ord, 'WORK' as phone_type from dual
                   union all select 4 as ord, 'SWK' as phone_type from dual
                   union all select 5 as ord, 'OTR' as phone_type from dual
                   union all select 6 as ord, 'FAX' as phone_type from dual) b2
            where a2.emplid = a.emplid
            and b2.phone_type = a2.phone_type))
;