Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table students (
  id integer primary key,
  name text not null,
  major text not null
);

CREATE TABLE


create table gpa (
  student_id integer not null references students,
  gpa numeric(2,1) not null,
  gpa_at timestamp not null,
  
  unique(student_id, gpa_at)
);

CREATE TABLE


insert into students values 
  (1, 'Someone', 'Underwater Basket Weaving'), 
  (2, 'Student McGee', 'Disestablishentarianism Studies');

INSERT 0 2


insert into gpa values
(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')

INSERT 0 5


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


id
name
major
gpa


1
Someone
Underwater Basket Weaving
4.0

2
Student McGee
Disestablishentarianism Studies
3.0



SELECT 2


s

ERROR:  syntax error at or near "s"
LINE 1: s
        ^

id	name	major	gpa
1	Someone	Underwater Basket Weaving	4.0
2	Student McGee	Disestablishentarianism Studies	3.0