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 "TABLE" (ID, "LEVEL", name, designation) AS
SELECT 1, 'A', 'Alice', 'AA' FROM DUAL UNION ALL
SELECT 1, 'B', 'Beryl', 'BB' FROM DUAL UNION ALL
SELECT 1, 'C', 'Carol', 'CC' FROM DUAL UNION ALL
SELECT 2, 'E', 'Emily', 'EE' FROM DUAL UNION ALL
SELECT 2, 'D', 'Debra', 'DD' FROM DUAL UNION ALL
SELECT 3, 'F', 'Fiona', 'FF' FROM DUAL;
6 rows affected

SELECT ID,
       MIN("LEVEL")     KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS "LEVEL",
       MIN(NAME)        KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS name,
       MIN(DESIGNATION) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS designation
FROM   "TABLE"
GROUP BY
       ID
HAVING MIN("LEVEL")     KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) <> '1';


ID
LEVEL
NAME
DESIGNATION


1
A
Alice
AA

2
E
Emily
EE

3
F
Fiona
FF