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 2 · Accepted Answer · answered Nov 19 '18 at 13:22

2

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

248,760
80
417
510

Perfect ! I just add ORDER BY date desc at the end ;) Many thanks @Salman-A – nickko Nov 19 '18 at 14:47

MySQL find maximum date per month

1 Answers1