example.sql

SELECT b.MemberCardNumber,
       b.MemberName,
       b.MemberSurname,
       COALESCE (b.MobilePhone, (SELECT i.MobilePhone FROM TerminalMemberTable i WHERE UPPER (i.MemberName) = UPPER (b.MemberName) AND UPPER (i.MemberSurname) = UPPER (b.MemberSurname) AND rownum <= 1 ORDER BY i.MobilePhone DESC NULLS LAST))    MobilePhone,
       COALESCE(b.HomePhone, (SELECT i.HomePhone FROM TerminalMemberTable i WHERE UPPER(i.MemberName) = UPPER(b.MemberName) AND UPPER(i.MemberSurname) = UPPER(b.MemberSurname) AND rownum <= 1 ORDER BY i.HomePhone DESC NULLS LAST))  HomePhone,
       COALESCE(b.EmailAddress, (SELECT i.EmailAddress FROM TerminalMemberTable i WHERE UPPER(i.MemberName) = UPPER(b.MemberName) AND UPPER(i.MemberSurname) = UPPER(b.MemberSurname) AND rownum <= 1 ORDER BY i.EmailAddress DESC NULLS LAST))    EmailAddress
FROM    (
        SELECT MemberCardNumber,
               MemberName,
               MemberSurname,
               MobilePhone,
               HomePhone,
               EmailAddress,
               RANK() OVER (PARTITION BY UPPER(MemberName), UPPER(MemberSurname) ORDER BY LastTransaction DESC) TransactionOrder
          FROM TerminalMemberTable
         WHERE IsActive = 1 AND LastTransaction IS NOT NULL
    ) b
WHERE TransactionOrder = 1