MySQL get records of last date of each month

Question

I need to fetch records of last date of each month based on the dates of records in the database table. Now, if I fetch the last dates of each month in my table using following query:

SELECT MAX(`date`) AS max_date 
FROM cpr
WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
GROUP BY YEAR(`date`), MONTH(`date`);

It returns the last dates of each month perfectly as below:

max_date
2023-01-31
2023-02-27
2023-03-31
2023-04-27
2023-05-31
2023-06-06

Now, If I use the same query to get rest data on that date as following, it return only the records of 2023-01-31, even after using ORDER BY `date` DESC in above query.

Below are the queries I'm trying:

SELECT * FROM cpr
INNER JOIN (
    SELECT MAX(`date`) AS max_date 
    FROM cpr
    WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
    GROUP BY YEAR(`date`), MONTH(`date`)
)
AS monthwise ON cpr.`date` = monthwise.max_date

another one:

SELECT * FROM cpr
WHERE `date` IN (
    SELECT MAX(`date`)
    FROM cpr
    WHERE YEAR(`date`) = YEAR(CURRENT_DATE())
    GROUP BY YEAR(`date`), MONTH(`date`)
)

Any help will be appreciated. TIA :)

Answer 1

Just need to replace * with cpr.* in SELECT statement as below:

SELECT cpr.*
FROM cpr
INNER JOIN ( 
    SELECT MAX(`date`) AS max_date 
    FROM cpr WHERE YEAR(`date`) = YEAR(CURRENT_DATE()) 
    GROUP BY YEAR(`date`), MONTH(`date`) 
) AS max_dates ON cpr.`date` = max_dates.max_date