select * from v$version

WITH 
Torso ( pk1, pk2 ) AS ( 
SELECT 'A', 'B' FROM DUAL UNION ALL 
SELECT 'B', 'B' FROM DUAL UNION ALL 
SELECt 'C', 'A' FROM DUAL 
), 
appendage ( pk1, pk2 ) AS ( 
SELECT 'A', 'A' FROM DUAL UNION ALL 
SELECT 'A', 'C' FROM DUAL UNION ALL 
SELECT 'A', 'D' FROM DUAL UNION ALL 
SELECT 'B', 'B' FROM DUAL UNION ALL 
SELECT 'B', 'C' FROM DUAL UNION ALL 
SELECt 'C', 'A' FROM DUAL 
), 
T AS ( 
SELECT t.* 
     , ASCII(pk2) as pk2ascii 
  FROM Torso T 
), 
A AS ( 
SELECT t.* 
     , ASCII(pk2) as pk2ascii 
  FROM appendage T 
), 
leftist AS ( 
SELECT t.pk1, t.pk2, a.pk2 
  FROM T 
  LEFT OUTER 
  JOIN A 
    ON A.pk1 = T.pk1 
   AND A.pk2 = T.pk2 
 ORDER BY 1, 2 
), 
partialist AS ( 
SELECT t.pk1, t.pk2 t_pk2, a.pk2 a_pk2 
     , ROW_NUMBER()OVER 
        ( PARTITION BY t.pk1 
              ORDER BY ABS ( t.pk2ascii - a.pk2ascii ) ASC 
                     , t.pk2ascii, a.pk2ascii 
        ) AS r# 
  FROM T 
  LEFT OUTER 
  JOIN A 
    ON A.pk1 = T.pk1 
 ORDER BY 1, 2 
), 
xapply AS ( 
SELECT t.pk1, t.pk2 t_pk2, aa.pk2 a_pk2 
  FROM T 
  CROSS APPLY ( SELECT a.pk1, a.pk2 
                  FROM A 
                 WHERE a.pk1 = t.pk1 
                 ORDER BY ABS ( t.pk2ascii - a.pk2ascii ) ASC 
                        , t.pk2ascii, a.pk2ascii 
                FETCH FIRST 1 ROWS ONLY 
              ) aa 
 ORDER BY 1, 2 
) 
SELECT 'standard' AS method, pk1, t_pk2, a_pk2 
  FROM partialist 
 WHERE r# = 1 
UNION ALL 
SELECT 'cross apply' AS method, xapply.* 
  FROM xapply 
 ORDER BY 1,2,3