I need to find in which subject the student has scored the highest marks in matillion

Question

I need to find in which subject the student has scored the most marks(only the subject name is enough) using matillion My data looks like this

studentid   maths   science art computer sports
1             55     68      59   75     62
2             75     68      79   56     89
3             89     85      74   32     56
4             89     92      86   75     12
5             99     100     45  68      45

And I want my result to look like this

studentid     highestmark
1              computer
2              sports
3              maths
4              science                     
5              science 

  

Answer 1

I have added to Gordon's answer to get highest mark too.

select t.*, (case greatest(maths, science, art, computer, sports) when maths then 'maths' when science then 'science' when art then 'art' when computer then 'computer' when sports then 'sports' end) as highestmark,

greatest(maths, science, art, computer, sports) highest_mark from t;

Answer 2

In Snowflake, or most SQL dialects, you can use greatest():

select t.*,
       (case greatest(maths, science, art, computer, sports)
           when maths then 'maths'
           when science then 'science'
           when art then 'art'
           when computer then 'computer'
           when sports then 'sports'
        end) as highestmark
from t;