SQL to Select Rows Conditionally Based On Column Value

Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority.

A typical example is selecting one contact number for a customer, based on contact types.

The possible contact types are:

• mobile (MOB)
• residence phone (RES)
• office phone (OFF)

The rules are:

(1) if a customer’s contact list includes a mobile number, use that as primary contact — even if a residence phone or office phone have been provided

(2) if a customer’s contact list does not include a mobile number, but includes a residence phone and office phone, select the residence phone

(3) select office phone only if neither mobile number nor residence phone have been provided

Solution: RANK() and Filter!

A simple SQL solution to select rows conditionally can be implemented by assigning a priority to each customer’s contact types, using the RANK() analytic function along with DECODE, then selecting only the rows with top priority. The inner SQL can be encapsulated in a WITH clause for better readability.

See it in action…

Table:

SQL> desc contact
 Name                                      Null?    Type
 --------------------------------- -------- -------------

 CUSTID                                     VARCHAR2(10)
 CONTACT_TYPE                               VARCHAR2(3)
 CONTACT_VALUE                              VARCHAR2(10)

Sample data:

SQL> select * from contact;

CUSTID CONTACT_TYPE   CONTACT_VALUE
------ -------------- --------------
1      RES            651213
1      MOB            652236
1      OFF            652999
2      OFF            818453
3      OFF            772453
3      RES            241444
4      RES            342111
4      MOB            215665

8 rows selected.

SQL:

with ranked_contacts as
(select t.*
      , rank() over 
       (
        partition by custid
        order by 
          decode(contact_type
                , 'MOB', 1
                , 'RES', 2
                , 'OFF', 3)
        ) priority
 from contact t) 
select custid
  , contact_type primary_contact
  , contact_value
from ranked_contacts
where priority = 1;

Execution Result:

SQL> with ranked_contacts as
  2  (select t.*
  3        , rank() over
  4         (
  5          partition by custid
  6          order by
  7            decode(contact_type
  8                  , 'MOB', 1
  9                  , 'RES', 2
 10                  , 'OFF', 3)
 11          ) priority
 12   from contact t)
 13  select custid
 14    , contact_type primary_contact
 15    , contact_value
 16  from ranked_contacts
 17  where priority = 1;

CUSTID PRIMARY_CONTACT CONTACT_VALUE
------ --------------- --------------
1      MOB             652236
2      OFF             818453
3      RES             241444
4      MOB             215665

Notes

For more complex conditions to derive the priority, consider using CASE statement in place of DECODE.