Postgres SQL Select two fields by most recent date, one field must be unique

I need to find which student belongs to which homeroom, as of the latest data inputted.

Starting with this dataset:

student homeroom    date
Alice   200         2020-02-25
Alice   100         2019-09-16
Bob     100         2019-10-20
Bob     100         2020-02-01

I want to only retrieve Alice’s most recent homeroom and Bob’s most recent homeroom:

student homeroom    date
Alice   200         2020-02-25
Bob     100         2020-02-01

The typical answer of how to find the most recent record only applies to when you’re looking for one field’s recency:

SELECT
     student, homeroom, MAX(date::date) AS date
   FROM
     homeroom
   GROUP BY
     student, homeroom

This is not what I want:

student homeroom    date
Alice   200         2020-02-25
Alice   100         2019-09-16
Bob     100         2020-02-01

Answer

You can use distinct on:

select distinct on (student) hr.*
from homeroom hr
order by student, date desc;

distinct on is a very handy Postgres extension. It returns on row for the keys in the distinct on list. The row is based on the ordering criteria in the order by.