CODEWITHSUNDEEP
sqlpostgresql
Zeswen
Zeswen

Reputation: 271

Get lowest value in left join from intermediate table

Given the following tables in PostgreSQL 13:

+---------+--------------+------+
| student | student_exam | exam |
+---------+--------------+------+
| id      | student_id   | id   |
|         | exam_id      | name |
|         | grade        |      |
+---------+--------------+------+

I want to fetch each student's lowest grade's exam, with the following rows as result:

+------------+-------+-----------+
| student_id | grade | exam_name |
+------------+-------+-----------+
| ...        | ...   | ...       |
+------------+-------+-----------+

My closest approach, not checking if its the minimum grade:

select
    student.id as student_id,
    student_exam.grade as grade,
    exam.name as exam_name
from
    student s
    left join student_exam as se on se.student_id = s.id
    left join exam as e on se.exam_id = e.id

Thanks in advance :)

Upvotes: 0

Views: 43

Answers (2)

Popeye
Popeye

Reputation: 35900

Why LEFT JOIN? (data without student_exam is of not useful to you) You can use the INNER JOIN and NOT EXISTS as follows:

select
    s.id as student_id,
    se.grade as grade,
    e.name as exam_name
from
    student s
    join student_exam as se on se.student_id = s.id
    join exam as e on se.exam_id = e.id
 WHERE NOT EXISTS
    (select 1 from student_exam se_ 
      where se_.student_id = se.student_id
        and se_.grade < se.grade )

Or you can use analytical function as follows:

select * from
(select
    s.id as student_id,
    se.grade as grade,
    e.name as exam_name,
    rank() over (partition by s.id order by se.grade) as rn
from
    student s
    join student_exam as se on se.student_id = s.id
    join exam as e on se.exam_id = e.id) t
where rn = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use distinct on:

select distinct on (s.id) s.id as student_id,
       se.grade as grade, e.name as exam_name
from student s left join
     student_exam se
     on se.student_id = s.id left join
     exam e
     on se.exam_id = e.id
order by s.id, se.grade;

Distinct on is a convenient Postgres extension. It returns one row per combination of values of the expression(s) in parentheses. This row is the "first" row for each combination of values. The order by specifies which row -- the first order by keys need to match the values in the distinct on. The remaining keys define "first".

Upvotes: 1

Related Questions