How to get all students with their recent grades

Question

my table looks like that:

id | type    | price    | effective_date 
1  | a       | 0.05     | 2020-12-15
2  | b       | 0.05     | 1990-11-15
3  | c       | 0.05     | 1990-02-15
4  | d       | 0.05     | 1990-05-15
5  | a       | 0.05     | 2001-01-04
6  | b       | 0.05     | 1990-02-12
7  | a       | 0.05     | 2004-02-11
8  | a       | 0.05     | 2054-02-07

so I have 4 types (a,b,c,d) and 8 rows. I would like select rows with highest effective_date for each type so the result would look like:

id | type    | price    | effective_date
8  | a       | 0.05     | 2054-02-07
2  | b       | 0.05     | 1990-11-15
3  | c       | 0.05     | 1990-02-15
4  | d       | 0.05     | 1990-05-15

how to do it? Thanks.

Answer 1

One option uses window functions:

select *
from (
    select t.*, rank() over(partition by type order by effective_date desc) rn
    from mytable t
) t 
where rn = 1

If there are top ties (that is, two or more rows having the same type and the maximum effective_date), the query returns them all.

Another solution is a correlated subquery. This works in most databases, even those that do not support window functions:

select t.*
from mytable t
where t.effective_date = (
    select max(t1.effective_date) from mytable t1 where t1.type = t.type
)