By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table ( ID, STUDENT_ID, SUBJECT_ID, CLASS_ID, EXPERTISE_LVL ) AS
SELECT 1, '1AAA', '55FFE', 'CLASS808', 2 FROM DUAL UNION ALL
SELECT 2, '1AAA', '55FFE', 'CLASS808', 2 FROM DUAL UNION ALL
SELECT 3, '2AAB', '49BB', 'CLASS890', 3 FROM DUAL UNION ALL
SELECT 4, '2AAB', '49BB', 'CLASS890', 3 FROM DUAL UNION ALL
SELECT 5, '2AAB', '49BB', 'CLASS890', 4 FROM DUAL UNION ALL
SELECT 6, '2AAB', '49BB', 'CLASS890', 3 FROM DUAL;
6 rows affected
DELETE FROM my_table
WHERE ROWID IN (
SELECT ROWID
FROM (
SELECT ROW_NUMBER() OVER (
PARTITION BY student_id, subject_id, class_id
ORDER BY expertise_lvl DESC
) AS rn
FROM my_table
)
WHERE rn > 1
)
4 rows affected
SELECT * FROM my_table;
| ID | STUDENT_ID | SUBJECT_ID | CLASS_ID | EXPERTISE_LVL |
|---|---|---|---|---|
| 2 | 1AAA | 55FFE | CLASS808 | 2 |
| 5 | 2AAB | 49BB | CLASS890 | 4 |