Search code examples
postgresqlmaxrow

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

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
Solution

  • 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