WITH T AS (SELECT 1 A, 'J' X FROM DUAL UNION ALL SELECT 2 A, 'R' X FROM DUAL UNION ALL SELECT 3 A, 'E' X FROM DUAL), A AS (SELECT 1 A, 'R' B, 'W' C FROM DUAL UNION ALL SELECT 1 A, 'E' B, 'W' C FROM DUAL UNION ALL SELECT 2 A, 'R' B, 'Q' C FROM DUAL UNION ALL SELECT 2 A, 'G' B, 'W' C FROM DUAL UNION ALL SELECT 3 A, 'H' B, 'G' C FROM DUAL UNION ALL SELECT 3 A, 'R' B, 'W' C FROM DUAL) SELECT C.A, C.B, C.C, C.X FROM (SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) RN, A.* FROM (SELECT A.*, T.X FROM T, A WHERE T.A = A.A) A) C WHERE RN = 1
select t1.a,t1.x,t2.b,t2.c from (select a.*,row_number(a.a) over(partition by a.a order by x) rn from a) t1, (select b.*,row_number(b.a) over(partition by b.a order by b) rn from b) t2 where t1.a=t2.a and t1.rn=t2.rn
select af.a, af.x, bf.b, bf.c from a af left join (select a, b, c, row_number() over(partition by a order by b) rn from b) bf on af.a = bf.a and bf.rn = 1
(SELECT 1 A, 'J' X
FROM DUAL
UNION ALL
SELECT 2 A, 'R' X
FROM DUAL
UNION ALL
SELECT 3 A, 'E' X
FROM DUAL),
A AS
(SELECT 1 A, 'R' B, 'W' C
FROM DUAL
UNION ALL
SELECT 1 A, 'E' B, 'W' C
FROM DUAL
UNION ALL
SELECT 2 A, 'R' B, 'Q' C
FROM DUAL
UNION ALL
SELECT 2 A, 'G' B, 'W' C
FROM DUAL
UNION ALL
SELECT 3 A, 'H' B, 'G' C
FROM DUAL
UNION ALL
SELECT 3 A, 'R' B, 'W' C
FROM DUAL)
SELECT C.A, C.B, C.C, C.X
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) RN, A.*
FROM (SELECT A.*, T.X FROM T, A WHERE T.A = A.A) A) C
WHERE RN = 1
(select a.*,row_number(a.a) over(partition by a.a order by x) rn from a) t1,
(select b.*,row_number(b.a) over(partition by b.a order by b) rn from b) t2
where t1.a=t2.a and t1.rn=t2.rn
select af.a, af.x, bf.b, bf.c
from a af
left join (select a, b, c, row_number() over(partition by a order by b) rn
from b) bf
on af.a = bf.a
and bf.rn = 1