How can i get the minimal value for each foreign key

database, postgresql, sql

Solution

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.

Problem

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])

Original source