MariaDB group by date order by date desc not working

Question

I have a table like this:

date
2020-03-11 10:41:26
2020-03-11 17:51:15
2020-03-12 10:45:28
2020-03-12 17:41:28

I want the result:

datech        date                cnt
2020年3月12日  2020-03-12 17:41:28 2
2020年3月11日  2020-03-11 17:51:15 2

I have tried

Select *, count(*) as cnt from (
  Select (DATE_FORMAT((date),'%Y年%c月%d日')) as datech, date
  from tablea where 1=1
  order by date desc
) as t         
GROUP BY datech order by date desc

but the result date is asc

datech         date                cnt
2020年3月12日  2020-03-12 10:45:28 2
2020年3月11日  2020-03-11 10:41:26 2

How can I desc date? Thanks.

add limit in subquery works https://stackoverflow.com/a/35202755/7955124 — AllenBooTung, Mar 13 '20 at 05:11

score 1 · Accepted Answer · answered Mar 13 '20 at 03:38

1

Try this:

Select datech, max(date) as date, count(*) as cnt from (
  Select (DATE_FORMAT((date),'%Y年%c月%d日')) as datech, date
  from tablea where 1=1
) as t         
GROUP BY datech order by date desc;

answered Mar 13 '20 at 03:38

ywbaek

2,971
3
9
28

MariaDB group by date order by date desc not working

1 Answers1