From a certain number of rows with the same primary key, how do I select the ones with the highest value in a certain column?

Question

I have a Students table and a Grades table.

Students has columns student_id(primary key), name, date_of_birth, address, email, and level.
Grades has columns student_id(primary/foreign key), course_id(primary/foreign key), and grade.

"Grades" looks like this:

student_id|course_id|grade
===========================
    1     |    1    |   A
    1     |    2    |   B
    1     |    3    |   A
    3     |    1    |   F
    3     |    3    |   C
    .
    .
    .
    .

This isn't the whole table, but you get the gist. I'm trying to write a query which SELECTS the name of a student and that student's highest grade. As I'm new to SQL this one is a little confusing for me. My attempt so far is this:

SELECT "Students".name, "Grades".grade FROM "Students" INNER JOIN
"Grades" ON "Students".student_id = "Grades".student_id GROUP BY name, 
grade HAVING MIN(grade) <= 'F';

It's wrong, and I know why, but I'm not sure where to go from here.

You have `Students` and `Grades`, not `Enroll` like it pops up in your code. — Erwin Brandstetter, Mar 16 '15 at 21:54
@ErwinBrandstetter whoops, fixed it. It's called Enroll in my code, but I changed it here to Grades for clarity. — Omar Kadery, Mar 17 '15 at 03:03

score 0 · Accepted Answer · answered Mar 16 '15 at 20:02

0

SELECT Students.name, min(Enroll.grade)  
  FROM Students 
 INNER JOIN Enroll 
    ON Students.student_id = Enroll.student_id 
 GROUP BY name,

answered Mar 16 '15 at 20:02

paparazzo

44,497
23
105
176

score 0 · Answer 2 · edited May 23 '17 at 11:56

how do I select the ones with the highest value in a certain column?

Bold emphasis mine.

GROUP BY is good to get the maximum for a single column. To get the rows with the maximum, you'd have to do more.

The simple solution in Postgres to get one row per student is with DISTINCT ON:

SELECT *
FROM   students s
JOIN  (
   SELECT DISTINCT ON (student_id) *
   FROM   grades
   ORDER  BY student_id, grade
   ) g USING (student_id);

The lowest happens to be the "highest" grade here.
To get the ones with the highest value (possible plural), use the window function rank():

SELECT *
FROM   students s
JOIN  (
   SELECT *, rank() OVER (PARTITION BY student_id ORDER BY grade) AS rnk
   FROM   grades
   ) g USING (student_id)
WHERE  g.rnk = 1;

From a certain number of rows with the same primary key, how do I select the ones with the highest value in a certain column?

2 Answers2