Search code examples
sqlwordpresssql-order-bygroup-concat

SQL sorting months, why am I getting December right after January?

I've got this query, that is working almost perfect:

SELECT tbl.y year, group_concat(month_posts SEPARATOR '-') dates
    FROM (
        SELECT YEAR(p.post_date) y, MONTH(p.post_date) m, concat(MONTH(p.post_date), ':', group_concat(p.id ORDER BY p.post_date ASC)) month_posts
        FROM prt_posts p    
        WHERE (p.post_status = 'publish' OR p.post_status = 'future') 
            AND p.post_type = 'EVENT'
            AND p.post_date <= DATE('2016-12-31 00:00:00')
        GROUP BY y, m
        ORDER BY y, m ASC
     ) tbl
GROUP BY tbl.y
ORDER BY tbl.y DESC

The output is year, dates with this format:

month:id,id,id-month:id,id,id-etc..

My problem is that on the results, I have december appearing right after january.

enter image description here

As you can see on the second row, I've got 1:128, 12:138 etc, so 1 (jan) and then 2 (Dec) right after. Why?

Solution

  • For now the solution for me was to add ORDER BY tbl.m to the first row:

    SELECT tbl.y year, group_concat(month_posts ORDER BY tbl.m SEPARATOR '-') dates