By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table studentdata_view(school_Name, Student_Name, Class_ID) as (
select 'AMM', 'joe', 'AMM-1-1-1' from dual union all
select 'AMM', 'joe', 'AMM-1-1-2' from dual union all
select 'AMM', 'Adam', 'AMM-1-1-1' from dual union all
select 'AMM', 'Adam', 'AMM-1-1-2' from dual union all
select 'AMM', 'Nancy', 'AMM-1-2-1' from dual union all
select 'AMM', 'Nancy', 'AMM-1-2-2' from dual union all
select 'AMM', 'Albert', 'AMM-1-2-1' from dual union all
select 'AMM', 'Albert', 'AMM-1-2-2' from dual union all
select 'IRB', 'Frank', 'IRB-1-1-1' from dual union all
select 'IRB', 'Frank', 'IRB-1-1-2' from dual union all
select 'IRB', 'Mike', 'IRB-1-1-1' from dual union all
select 'IRB', 'Mike', 'IRB-1-1-2' from dual )
12 rows affected
select *
from (
select school_name,student_name, class_id,
dense_rank() over (partition by school_name, class_id order by student_name) rnk,
count(1) over (partition by school_name, class_id) cnt,
row_number() over (partition by school_name, student_name order by class_id ) rn
from studentdata_view)
where cnt = 1
or (cnt = 2 and ((rnk = 1 and rn = 1) or (rnk = 2 and rn = 2)))
| SCHOOL_NAME | STUDENT_NAME | CLASS_ID | RNK | CNT | RN |
|---|---|---|---|---|---|
| AMM | Adam | AMM-1-1-1 | 1 | 2 | 1 |
| AMM | joe | AMM-1-1-2 | 2 | 2 | 2 |
| AMM | Albert | AMM-1-2-1 | 1 | 2 | 1 |
| AMM | Nancy | AMM-1-2-2 | 2 | 2 | 2 |
| IRB | Frank | IRB-1-1-1 | 1 | 2 | 1 |
| IRB | Mike | IRB-1-1-2 | 2 | 2 | 2 |