Prioritizing values in select statement in ORACLE

Question

Due to the JOIN for the communication method (Phone, email etc) we get alot of duplicates because a company can have multiple communication methods (CICM.CLIENT_VALUE).

My question is... Is there a way to incorporate in the select statement to where if out of the client_Value field, if the value = EMAIL & Phone then only pull EMAIL for that CUSTOMER_ID, if they dont have have email and only phone, then pull phone

Right now I can have CUSTOMER_ID = 1 and have 2 records with the same data but the client_value will be different because they have an email, and phone, so I am getting 2 returned records instead of prioritizing 1 over the other.

Here is an image to show what I am referencing...you can see all the blacked out fields have the same value on both records but the contact_type and value are different. I would like to prioritize and choose PHONE over EMAIL if available, and not get BOTH to reduce record return count...

If this needs clarification please advise.

SELECT DISTINCT INV.CUSTOMER_ID
     , INV.ADDRESS_ID
     , INV.NAME
     , INV.ADDRESS1
     , INV.ADDRESS2
     , INV.CITY
     , INV.STATE
     , INV.ZIP_CODE
     , INV.COUNTRY
     , CICM.CLIENT_VALUE as CONTACT_TYPE
     , CICM.VALUE
     , CICM.DESCRIPTION
     , INV.REGION_CODE
     , INV.SITE
     , LD.LSTINVDATE
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS SALESPROGRESS
     , SUM(INV.SALES) AS TOTALSALES
FROM IFSINFO.HB_INVOICING_ALL INV 
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD 
    ON (INV.COMPANY = LD.COMPANY) 
    AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
    ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
    AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
AND INV.STATE = '&State'
GROUP BY INV.COMPANY
       , INV.CUSTOMER_ID
       , INV.ADDRESS_ID
       , INV.NAME
       , INV.ADDRESS1
       , INV.ADDRESS2
       , INV.CITY
       , INV.STATE
       , INV.ZIP_CODE
       , INV.COUNTRY
       , CICM.CLIENT_VALUE
       , CICM.VALUE
       , CICM.DESCRIPTION
       , INV.REGION_CODE
       , INV.SITE
       , LD.LSTINVDATE  
       , INV.INVDATE

HAVING SUM(CASE WHEN INV.INVDATE >= TO_DATE('01-Oct-13')
                 AND INV.INVDATE < TO_DATE('30-Sep-15')
             THEN INV.SALES ELSE 0 
           END) = 0

Answer 1

If I have well understood your question, you should use the DECODE function in your query in the SELECT and GROUP BY sections.

NB: for performance purpose, you might create an index using that function.