title: Query to determine the id of the student who has the highest average in Physics, Chemistry and Mathematics
body: <p>I have table named <code>marks</code> with two columns <code>student_id</code> and <code>marks</code>.
  <code>student_id</code> column contains a unique id of the student
  <code>marks</code> column contains marks of the five different subject of the student Separated by comma. I tried multiple ways to determine the id of the student who has the highest average in Physics, Chemistry, and Mathematics. I am not able to get my output. Can someone please help here?
  Data looks like below:
  Table - <code>marks</code></p>
  
  <pre><code>Student Id | marks
  101        | 93,63,74,29,57
  102        | 63,66,34,89,27
  103        | 93,64,54,22,97
  104        | 43,93,34,78,87
  105        | 95,83,54,49,78
  </code></pre>
  
  <p>This can be interpreted as first-row student id 101.His/Her Physics marks - 93, Chemistry mark - 63, maths mark - 74, history - 29, philosophy - 57
  Below my query that I tried. First, I split the marks column into multiple columns. But I am not able to determine the id of the student who has the highest average in Physics, Chemistry, and Mathematics. I am not able to get my output.
  Expected Output:
  Highest mark in physics student id - 105
  Highest mark in chemistry student id - 104
  Highest mark in mathematics student id - 101</p>
  
  <p>Output should display only the studentid who is having the highest average of first three subject</p>
  
  <pre><code>DELIMITER $$
  CREATE FUNCTION SPLIT_STR(
    x VARCHAR(255),
    delim VARCHAR(12),
    pos INT
  )
  RETURNS VARCHAR(255) DETERMINISTIC
  BEGIN 
      RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
         LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
         delim, '');
  END$$
  
  DELIMITER ;
  SELECT student_id,SPLIT_STR(marks, ',', 1) as physics,
       SPLIT_STR(marks, ',', 2) as chemistry,
       SPLIT_STR(marks, ',', 3) as mathematics,
        SPLIT_STR(marks, ',', 4) as history ,
        SPLIT_STR(marks, ',', 5) as philosophy 
  FROM   marks;
  </code></pre>