Select last record of each month for each ID

Question

I'm trying to pull the last record of each month for each ID.

Below is the table I'm requesting from:

myTable
ID  date         data
1   2020-08-27   a
1   2020-08-28   b
1   2020-09-30   c
2   2020-08-29   d
2   2020-09-30   e

I would therefore like to get the following:

output
ID  date         data
1   2020-08-28   b
1   2020-09-30   c
2   2020-08-29   d
2   2020-09-30   e

The "complexity" here is that the last record of each month might not be the same for each ID.

So far, I only managed to get all the records available on the last date in each month in the database with the following request:

SELECT ID, date, data
from myTable
and date in (SELECT max(date) FROM myTable GROUP BY strftime('%Y-%m', date))

which gives me this

wrong output
ID  date         data
1   2020-09-30   c
2   2020-08-29   d
2   2020-09-30   e

Answer 1

With ROW_NUMBER() window function:

SELECT ID, date, data
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY ID, strftime('%Y%m', date) ORDER BY date DESC) rn
  FROM myTable 
)
WHERE rn = 1