How to substitute the values ​​of one column into others, sorting by the contents of these columns?

Question

PostgreSQL

Initial data

Студент | Математика | Физика | Химия
--------+------------+--------+-----------
 Вася   | 80         | 52     | 69
 Коля   | 50         | 90     | 95
 Петя   | 62         | 98     | 42

How to get data of the form from them

Математика | Физика | Химия
-----------+--------+----------
 Вася      | Коля   | Петя
 Петя      | Петя   | Вася
 Коля      | Вася   | Коля

That is, in each column, display the names of students in the order of their performance in the subject. I can't even figure out how to approach the issue.

Answer 1

Your table of the expected result does not match the original data.
Well, for example, like this:

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;

математика | физика | химия 
------------+--------+-------
 Вася       | Петя   | Коля
 Петя       | Коля   | Вася
 Коля       | Вася   | Петя

Answer 2

In short, this is how it turns out on tsql. Works

--табличка с оценками
 declare @marks table 
 (
  Id int,
  Name varchar(50),
  Math int,
  Physics int,
  Chemistry int
 )

--наполняем
 insert into @marks
 values
 (1, 'Вася', 80, 52, 69),
 (2, 'Коля', 50, 90, 95),
 (3, 'Петя', 62, 98, 42)
------------------------------------------------------------теперь Miracle
--таблица рейтингов
  declare @ratings table 
 (
  Id int,
  Math varchar(50),
  Physics varchar(50),
  Chemistry varchar(50)
 )

insert into @ratings(id)
 values
 (1), (2), (3)

declare @id int; --ид записи
set @id = 1;

WHILE (@id) <= 3
BEGIN  
   UPDATE @ratings
      SET 
    Math = (select Name from @marks order by Math desc OFFSET (select(@id-1)) ROWS FETCH NEXT 1 ROWS ONLY),
    Physics = (select Name from @marks order by Physics desc OFFSET (select(@id-1)) ROWS FETCH NEXT 1 ROWS ONLY),
    Chemistry = (select Name from @marks order by Chemistry desc OFFSET (select(@id-1)) ROWS FETCH NEXT 1 ROWS ONLY)
    where Id = @id;

  set @id = @id + 1;	  
END  

select * from @marks
select * from @ratings

Soryan, I did not immediately see what you have on postgre. But I'm sure it won't be difficult for you to adapt the solution