How can i get the minimal value for each foreign key

Question

Given the following table:

student   discipline   mark
-------   ----------   ----
   1         math       5 
   1      phylosophy    4
   1      literature    3
   2         math       2
   2      phylosophy    5
   2      literature    5

What is the best way to get the minimal mark for each student? (result should be [3,2])

Answer 1

Use the MIN function.

SELECT student, MIN(mark)
FROM result_table
GROUP BY student

If you need the discipline they got the lowest mark in you can do the following:

SELECT result_table.*
FROM result_table 
JOIN (SELECT student, MIN(mark) as min_mark
  FROM result_table
  GROUP BY student) lowest_result ON result_table.student = lowest_result.student 
AND result_table.mark = lowest_result.min_mark

This will show the results where the student had the lowest mark. Note that this will return two rows for a student if they have the same lowest mark in multiple subject. To avoid this you can add another MIN around the discipline and GROUP BY student and mark.