SQL Select all MIN Values for each group-CodePudding

So I need to select all the students, having the minimum grade for each prof. For example, if Augustinus had two students with grade 1.0, then I would like to see both in the result.

Table of my data

What the result could look like, if the LIMIT was set to 10

So what I basically want is to see the best students that each prof has.

What I have tried is the following:

SELECT professor, student, min(note)
FROM temp
GROUP BY professor
ORDER BY note

The problem of course being that I only get one minimum value for each prof and not all minimum values.

*temp is just the table name

CodePudding user response：

One way to solve these types of problems is to use a subquery to rank the grades for each class in a descending order. This involves a window function. With a second query you can limit the results based on your criteria of 10.

SELECT professor, student, note
FROM
(
    SELECT professor,student,note,
    row_number() over(partition by professor order by note desc) as downwardrank
) as rankings
WHERE
    downwardrank <= 10

CodePudding user response：

Just found a solution myself:

SELECT professor, student, note
FROM temp
WHERE (professor, note) IN
    (SELECT professor, min(note)
    FROM temp
    GROUP BY professor
    ORDER BY note)
ORDER BY note, professor, student
LIMIT 10