Discovery of the day: GROUP BY ... DESC

May 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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

4 Comments to "Discovery of the day: GROUP BY ... DESC"

  1. Kostja Osipov wrote:

    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.

  2. Roland Bouman wrote:

    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

  3. Mark Callaghan wrote:

    @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.

  4. Roland Bouman wrote:

    @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.

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org