Postgresql按特定列选择具有最小值的行？

Question

我有这样的数据

id  student  grade subject
-----------------------------
0   Amy      5     Art History
1   Amy      2     Ethics
2   Jhon     7     Calculus
3   Joe      4     Poetry
4   Joe      2     Cooking
5   Jhon     2     PE

找出每个人的最低分数

Results 
--------------
1   Amy      2     Ethics
5   Jhon     2     PE
4   Joe      2     Cooking

试用解决方案

SELECT *, min(grade)
FROM data
GROUP BY student

我得到了错误must appear in the GROUP BY clause or be used in an aggregate...

Answer 1

如果您不担心领带，那么使用PostgreSQL扩展来表示DISTINCT ON

SELECT DISTINCT ON (id) id, student, grade, subject
FROM table
ORDER BY id, grade;