Redshift 1:1 left join on right table with duplicates

Question

I have a table with student information (where each student id is unique) and a table with GPAs (which represent GPAs with different timestamps, so the student ids are not unique).

Student info table:

id name         major
1  John Smith   Math
2  Bob Johnson  Science

GPA table:

student_id gpa  timestamp
1          4.0  2024-03-22
1          4.1  2024-02-22
1          3.9  2024-01-22
2          3.0  2024-01-22
2          3.2  2023-12-22

I want a table that returns the student information, with their most recent GPA

id name         major   gpa
1  John Smith   Math    4.0
2  Bob Johnson  Science 3.0

I tried using a left join but that joined each student entry, with each gpa entry, e.g

select student.id, student.name, student.major, gpa_table.gpa from students
left join gpa_table on student.id = gpa_table.gpa

which returns

id name         major   gpa
1  John Smith   Math    4.0
1  John Smith   Math    4.1

I tried selecting distinct students from the GPA table first with select distinct, but that returns duplicate rows anyway (redshift select distinct returns repeated values)

Is there a way to ensure a 1:1 mapping with the left join, and also ensuring that the left table is joined with only the most recent entry from the right table?

Answer 1

First, give the student's GPAs an order using the row_number window function.

  select
    student_id, gpa,
    row_number() over (
      partition by student_id
      order by gpa_at desc
    ) as rownum
  from gpa

Then we can left join with that, but only those with a row_number of 1.

with ordered_gpa as (
  select
    student_id, gpa,
    row_number() over (
      partition by student_id
      order by gpa_at desc
    ) as rownum
  from gpa
)
select
  students.id, students.name, students.major, ordered_gpa.gpa as gpa
from students
left join ordered_gpa on ordered_gpa.student_id = students.id and ordered_gpa.rownum = 1
order by students.id

Demonstration.