У вас табличка ожидаемого результата не соответствует исходным данным.
Ну, например, так:
with origtable (name, math, physic, chemie) as (values ('Вася', 80, 52, 69), ('Коля', 50, 90, 95), ('Петя', 62,98, 42))
, orders as (
select name,
row_number() over (order by math desc) as mathid,
row_number() over (order by physic desc) as phid,
row_number() over (order by chemie desc) as chid
from origtable
)
select m.name as "математика", p.name as "физика", c.name as "химия" from orders as m
join orders as p on m.mathid=p.phid
join orders as c on m.mathid=c.chid
order by m.mathid;
математика | физика | химия
------------+--------+-------
Вася | Петя | Коля
Петя | Коля | Вася
Коля | Вася | Петя
