Question 183758 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL get records of last date of each month

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 🙂

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0

    Use window functions! This is simpler, and in general more efficient than a subquery and a self-join:

    select *
from (
    select c.*, 
        row_number() over(partition by year(date), month(date) order by date desc) rn
    from cpr c
) c
where rn = 1

    Note: window functions are available in MySQL starting version 8.0.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search