CODEWITHSUNDEEP
sqlpostgresql
Pipipichu
Pipipichu

Reputation: 61

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

Upvotes: 1

Views: 86

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5155

You can use row_number()

SELECT student, homeroom, date from
(SELECT
 student, homeroom, date, row_number() over(partition by student order by homeroom, date 
desc) as rnk
FROM
 homeroom) qry WHERE rnk = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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.

Upvotes: 2

Related Questions