Table

id | usr | how | dt ----------------------------- 1 | 101 | 10 | 2016-10-16 ----------------------------- 2 | 101 | 20 | 2016-10-16 ----------------------------- 3 | 101 | 30 | 2016-10-17

You need to make such a request:

 SELECT id,how, MONTH(dt) AS `dt` FROM stat WHERE usr = '101' GROUP BY YEAR(dt), MONTH(dt)

But it gives out in the grouping the first lines, such as id 1, and in fact it is necessary id 3. ORDER BY id DESC does not help

I tried it, but also not

 SELECT * from ( SELECT id,how, MONTH(dt) AS `dt` FROM stat WHERE usr = '101' ORDER BY id DESC) AS te GROUP BY YEAR(te.dt), MONTH(te.dt) ORDER BY te.id DESC

    1 answer 1

    Most DBMSs (and MySQL with the only_full_group_by option enabled) would generally give you an error for such a request. Because it is impossible to use columns in the output without group functions that are not included in the group by clause (in MySQL it is possible, but the result is unpredictable). You must explicitly explain SQL which value from several in the group you want to get.

    Use aggregate functions, for example:

     SELECT max(id) as id, max(how) as how, MONTH(dt) AS `dt` FROM stat WHERE usr = 101 GROUP BY YEAR(dt), MONTH(dt)

    And to get the latest (by id) records each month, you can do this:

     select * from table A join (select max(id) from table where usr=101 group by extract(year_month from dt)) B on A.id=B.id

    As for the pre-sorting of rows before grouping, it does not make sense, because the group by itself performs the sorting in its own order and does not pay the slightest attention to the previous order of rows.

    • id gives true, and the data from the first line of type 3 | 10 | 10 - Rammsteinik
    • one
      @Rammsteinik Maybe you need exactly the last line of each month, so there are 2-3 questions and answers per month. for example ru.stackoverflow.com/questions/562926/… ... - Mike
    • right, I'll try - Rammsteinik