By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tablename(college varchar(10), name varchar(10)
, salary_college1 int, salary_college2 int)
insert into tablename values ( 'c1', 'n1', 50, 60)
insert into tablename values ( 'c1', 'n1', 50, 60)
insert into tablename values ( 'c1', 'n1', 50, 60)
insert into tablename values ( 'c1', 'n1', 50, 60)
insert into tablename values ( 'c2', 'n2', 90, 60)
insert into tablename values ( 'c2', 'n2', 120, 40)
select T1.college
, T1.name
, T1.salary_college1
, T1.salary_college2
from (SELECT distinct TN.college
, TN.name
, rank() over (partition by college order by (case when college = 'c1'
then salary_college1
else salary_college2
end) desc
) rn
, salary_college1
, salary_college2
FROM tablename TN) T1
where T1.rn = 1
order by T1.college;
| college | name | salary_college1 | salary_college2 |
|---|---|---|---|
| c1 | n1 | 50 | 60 |
| c2 | n2 | 90 | 60 |