MySQL find maximum date per month

Question

i have a table :

date         number
2018-11-01   5
2018-11-30   7
2018-10-05   3
2018-10-28   1
2018-09-01   8
2018-09-05   9

how to sort the value of the last day for each month ?

i try :

$mysqli->query("
SELECT * 
  FROM stats 
 GROUP 
    BY YEAR(date)
     , MONTH(date) desc 
 ORDER 
    BY date desc");

and also tryed with MAX() but without success :(

i would like to have in my while rows this result :

 date        number
 2018-11-30  7
 2018-10-28  1
 2018-09-05  9

so just keep the value(number) of the last day for each month.

Thanks !

Using `SELECT * ... GROUP BY ...` is always wrong.. [(My)SQL mistakes. Do you use GROUP BY correctly?](https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/) — Raymond Nijland, Nov 19 '18 at 13:22
What should happen which ties? Meaning WHERE max date is the same within the group., — Raymond Nijland, Nov 19 '18 at 13:24

score 3 · Accepted Answer · answered Nov 19 '18 at 13:22

3

Calculate max date for each year-month pair and join with it:

SELECT date, number
FROM stats
INNER JOIN (
    SELECT MAX(date) AS maxdate
    FROM stats
    GROUP BY YEAR(date), MONTH(date)
) x ON stats.date = maxdate

answered Nov 19 '18 at 13:22

Salman A

262,204
82
430
521

Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A – nickko Nov 19 '18 at 14:47
Thats also pretty neat: `select YEAR(date) year, MONTH(date) month, MAX(date) max_date, number from stats group by year, month` – Zacharias Feb 07 '23 at 10:17
1

you have `number` in select clause but which number would it be? the `2018-11` group contains 5 and 7; MySQL is free to return any of those values. – Salman A Feb 07 '23 at 10:20
1

The solution which I now finally prefer, when you need to "SELECT *" is using a subquery instead of a join, so that you don't have maxdate in each row, and your data structure is more "original": `SELECT * FROM stats WHERE date IN (SELECT MAX(date) FROM stats GROUP BY YEAR(date), MONTH(date))` – Zacharias Mar 14 '23 at 15:05

MySQL find maximum date per month

1 Answers1