How to find the maximum value in a row in plpgsql?

Question

I am having the following table

Student Id	Subject A	Subject B	Subject C	Subject D
1	98	87	76	100
2	90	100	64	71

This table contains information about the marks scored by students in various subject.

Now I need to find the maximum mark scored by each student among the various subject

I need the output to be like

Student Id	Subjects	Maximum Mark
1	Subject D	100
2	Subject B	100

Answer 1

This would need a lateral join with the list of subject/mark from the corresponding columns. This works for any number of Subject X columns.

select ext."Student Id", l.* 
from the_table ext, lateral
(
   select l.key, l.value::numeric 
   from the_table t, lateral json_each_text(to_json(t)) l(key, value)
   where "Student Id" = ext."Student Id" and l.key like 'Subject%'
   order by l.value::numeric desc limit 1
) l;

DB-Fiddle demo