Skip to content

# Discovery of the day: GROUP BY … DESC

MySQL SQL Syntax May 4, 2010May 4, 2010

I happened on a query where, by mistake, an

    SELECT ... ORDER BY x DESC LIMIT 1

was written as

    SELECT ... GROUP BY x DESC LIMIT 1

And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It’s documented.

In MySQL, GROUP BY results are sorted according to the group statement. You can override this by adding ORDER BY NULL (see past post). I wasn’t aware you can actually control the sort order.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
4
Leave a Reply

4 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Kostja Osipov

And how is it different form having an ORDER BY clause as well?
MySQL can merge ORDER and GROUP clauses, so as long as your statement is optimized OK, I would recommend you use an explicit ORDER BY and stay within the borders of the standard SQL.

MySQL has a lot of extensions to the grammar, which, if you look a bit deeper, are more resemblant with bugs than with features.

Guest
Roland Bouman

Yes.

It seems like such a nice syntax…until you realize that the commitment to ordering the result according to the GROUP BY clause actually takes away other opportunities to optimize the statement. Adding ORDER BY NULL to prevent that, is not something any sane query tool will figure out.

Anyway, perhaps it’s not too late to have it fixed. Please add your comments here if you agree:

http://bugs.mysql.com/bug.php?id=30477

Guest
Mark Callaghan

@Roland – I read your comment and the bug and my first thought was that MySQL didn’t commit themselves to ordering the GROUP BY result. Then I read the docs and realized that they did. Too bad.

Oracle made a few users unhappy when hash-based group by algorithms were used.

Guest
Roland Bouman

@Mark – yes, it is all very unfortunate.

Also see:

http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary-tables/

Personally, I would love to see this “feature” removed. I wouldn’t mind if backward compatibility would be broken for that, but I understand why some people would be reluctant to do that. Anyway, I think the proposal I did in #30477 is a pretty reasonable, considering all circumstances.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.