Get the max month from a query that returns several years

Question

I have a table with dates, one date per month (some months will be missing but that is expected) but several years are return. I need to get the latest month only. So if I have data for say months 8, 7, 6, etc. in 2020 then return those startDate. And for months 10, 11, and 12 it should return the StartDate from 2019 or wherever it finds it that is the latest. id and courseLength are part of the table but irrelevant for this task. StartDate is of type date.

This is the top 15 rows of the table

id  StartDate   courseLength
153 2020-08-31  63
153 2020-07-31  35
153 2020-06-30  60
153 2020-05-31  17
153 2020-03-31  51
153 2020-01-31  59
153 2019-12-31  30
153 2019-10-31  51
153 2019-08-31  59
153 2019-06-30  54
153 2019-05-31  17
153 2019-03-31  56
153 2019-01-31  55
153 2018-12-31  27
153 2018-10-31  54

And this is what I am expecting

id  StartDate   courseLength
153 2020-08-31  63
153 2020-07-31  35
153 2020-06-30  60
153 2020-05-31  17
153 2020-03-31  51
153 2020-01-31  59
153 2019-12-31  30
153 2019-10-31  51
153 2018-11-30  65
153 2018-09-31  53
153 2019-05-31  17
153 2018-04-30  13

Answer 1

You can use window functions:

select *
from (
    select t.*,
        row_number() over(partition by id, month(startdate) order by startdate desc) rn
    from mytable t
) t
where rn = 1