1

This is my query: 

Select Date_format(`date`, '%b %Y') as 'Categories',
..... as 'a1',....... as 'a2'
FROM table1
GROUP BY Date_format(`date`, '%b %Y')
ORDER BY Date_format(`date`, '%b %Y') ASC

Answer is alphabetical because, date_format convert date to string, I need sorting by date, but, group by is the problem. I need group by only for Date_format('date','%b %Y')

Please give a solution

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
c.moncado
  • 11
  • 2

3 Answers3

5

Of course it is alphabetical. You are ordering by a string. Try:

ORDER BY MIN(date) ASC

This will order by the date value.

Note that in the original version of your question, you had single quotes around date. That will not order by anything at all, because 'date' is a string with a four letter word in it.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The `MIN(date)` was what ultimately got this to work for me – ordering simply by `date` without using some kind of aggregator like `MIN()` (like some of these other answers suggest) throws an SQL error for me: "Expression ... of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause" – wosevision Jun 26 '20 at 18:22
  • @wosevision . . . You might consider accepting this answer. – Gordon Linoff Jun 26 '20 at 22:16
  • 1
    it wasn't my question to accept, but I've already added my upvote to this. – wosevision Jun 27 '20 at 14:24
0

Just do this:

Select Date_format(`date`,'%b %Y') as 'Categories',
..... as 'a1',....... as 'a2'
FROM table1
GROUP BY Date_format(`date`,'%b %Y')
ORDER BY `date` ASC
henrikenblom
  • 132
  • 8
  • 1
    Use backticks around table and column names, not single quotes. https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Barmar Aug 24 '17 at 18:56
  • Aah, yes. You are correct. They got converted during copy/paste from the original question... – henrikenblom Aug 24 '17 at 19:02
  • In this case, 'date' should be in 'SELECT' also, or not? Thanks – c.moncado Aug 24 '17 at 21:02
  • No, not necessarily. You can always order on any columns you like without having to include them in the select clause. – henrikenblom Aug 24 '17 at 21:16
-1

I solved this question putting double quotes 

Select Date_format(`date`, '%b %Y') as 'Categories',
..... as 'a1',....... as 'a2'
FROM table1
GROUP BY Date_format(`date`, '%b %Y')
ORDER BY "categories" ASC
daniel
  • 41
  • 1
  • 6