Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE person_table (id, name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Betty' FROM DUAL UNION ALL
SELECT 3, 'Carol' FROM DUAL UNION ALL
SELECT 4, 'Debra' FROM DUAL;
4 rows affected

CREATE TABLE file_table (file_name, person_id) AS
SELECT 'A', 1 FROM DUAL UNION ALL
SELECT 'B', 1 FROM DUAL UNION ALL
SELECT 'C', 1 FROM DUAL UNION ALL
SELECT 'D', 3 FROM DUAL;
4 rows affected

SELECT *
FROM   ( SELECT *
         FROM   person_table p
         WHERE  EXISTS(SELECT 1 FROM file_table f WHERE p.id = f.person_id)
         FETCH FIRST 2 ROWS ONLY ) p
       INNER JOIN file_table f
       ON  p.id = f.person_id;


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

1
Alice
C
1

3
Carol
D
3


SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk
  FROM   person_table p
         INNER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

1
Alice
C
1

3
Carol
D
3


SELECT *
FROM   ( SELECT *
         FROM   person_table p
         WHERE  EXISTS(SELECT 1 FROM file_table f WHERE p.id = f.person_id)
         FETCH FIRST 2 ROWS ONLY ) p
       CROSS JOIN LATERAL (
         SELECT *
         FROM   file_table f
         WHERE  p.id = f.person_id
         FETCH FIRST 2 ROWS ONLY
       );


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

3
Carol
D
3


SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk,
         ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY f.file_name) AS rn
  FROM   person_table p
         INNER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2
AND    rn <= 2


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

3
Carol
D
3


SELECT *
FROM   ( SELECT *
         FROM   person_table
         FETCH FIRST 2 ROWS ONLY ) p
       LEFT OUTER JOIN file_table f
       ON p.id = f.person_id;


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

1
Alice
C
1

2
Betty
null
null


SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk
  FROM   person_table p
         LEFT OUTER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

1
Alice
C
1

2
Betty
null
null


SELECT *
FROM   ( SELECT *
         FROM   person_table
         FETCH FIRST 2 ROWS ONLY ) p
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   file_table f
         WHERE  p.id = f.person_id
         FETCH FIRST 2 ROWS ONLY
       )
       ON (1 = 1);


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

2
Betty
null
null


SELECT id,
       name,
       file_name,
       person_id
FROM   (
  SELECT p.*,
         f.*,
         DENSE_RANK() OVER (ORDER BY p.name, p.id) AS rnk,
         ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY f.file_name) AS rn
  FROM   person_table p
         LEFT OUTER JOIN file_table f
         ON  p.id = f.person_id
)
WHERE  rnk <= 2
AND    rn <= 2


ID
NAME
FILE_NAME
PERSON_ID


1
Alice
A
1

1
Alice
B
1

2
Betty
null
null