Archives

The List

« MySQL Conference ‘07 Certification Exam Pass Rates | Main | MySQL Pop Quiz #7 »

MySQL Pop Quiz #6

By Carsten | February 21, 2008

Spanish flag Spanish translation by Marcos Besteiro

The following statement is completely valid and does what you expect:

SELECT DATE(creation_time) AS date,
       COUNT(*) AS num_signups
FROM signups
GROUP BY date
ORDER BY date;

In MySQL, you could leave out one clause of the above SELECT statement, yet still end up with the exact same result. Which clause is that?

Click here for the answer


If you have come across something that you think would make a good pop quiz question, send it to carsten (at) bitbybit (dot) dk. It’s OK if you don’t know the answer to your own question — that only makes things more interesting.More Pop Quiz entries.

Topics: MySQL, Pop Quiz |

13 Responses to “MySQL Pop Quiz #6”

  1. Diego Says:
    February 21st, 2008 at 5:39 pm

    “Adding DESC or ASC to the GROUP BY” - there is always something new to learn every day.

  2. Bill Karwin Says:
    February 21st, 2008 at 7:57 pm

    It should be noted that this syntax is a MySQL-ism ASC or DESC in the GROUP BY clause is not standard SQL. Standard SQL does not specify that GROUP BY causes a sort, that’s just a side effect of some implementations.

    Using ASC and DESC in the GROUP BY clause is *not* supported in the following databases:

    IBM DB2: no
    Firebird: no
    MS SQL Server: no
    Oracle: no
    PostgreSQL: no
    SQLite: no

  3. Sheeri Says:
    February 21st, 2008 at 8:42 pm

    You can take out ORDER BY because the GROUP BY will automatically order the columns in order to GROUP it. If the columns in the ORDER BY are the same as the GROUP BY you don’t need the ORDER BY.

    For the record, I’m MySQL 4 Pro certified and MySQL 5.0 DBA certified. :)

  4. Salle Says:
    February 21st, 2008 at 11:24 pm

    Just some comments on comments as I wouldn’t really dare to answer Carsten’s quiz.

    # Bill Karwin Says:
    Standard SQL does not specify that GROUP BY causes a sort, that’s just a side effect of some implementations.

    The good thing is that SQL standard does not forbid any particular order either.

    Since SQL complies to relational theory which on it’s turn is derived from the theory of sets it dictates that without ORDER BY clause the server is free to provide the result in any order it (the server) deems efficient.

    Here comes the beauty of the SQL standard.

    If the standard allows Any order why not making it predictable? Why not Ascending for example?

    After all 90% of users wouldn’t mind. Correct?

    Of course if you are among the rest 10% you don’t want to pay the extra cost for that default order and then … we have another Quiz question. Carsten can formulate it much better than me.

  5. Roland Bouman Says:
    February 22nd, 2008 at 3:28 am

    @salle

    “The good thing is that SQL standard does not forbid any particular order either.”

    yes, but it is IMO unfortunate that MySQL has committed itself to always sorting the result of a GROUP BY even in absense of an ORDER BY.

    Now I don’t know much about how GROUP BY is implemented. But I do know that this particular commitment compels every MySQL implementation to deliver this behavior in order to not break backwards compatibility

    Suppose I have a master and a detail table, joined by some condition, and we want to calculate some aggregate, let’s say, the COUNT(detail) for each master. Now let’s assume the master has a composite primary key (A,B). Ok. So now I accidentally write:

    SELECT M.A, M.B, COUNT(D.*)
    FROM M
    JOIN D
    ON (A,B)
    GROUP BY B,A

    Obviously, the order of the components of the primary key does jack for the result. We are grouping on M.A and M.B, so we are effectively returning each row from M. This result could be obtained superefficiently by simply scanning M’s primary key.

    Alas, the query accidentally groups by B,A and thus MySQL feels compelled to change the order of the result even though *the user never asked for that*, and is of course still *waiting* on the sorting operation.

    “If the standard allows Any order why not making it predictable? Why not Ascending for example?”

    Well, this is a god idea, and this would be the behavior that I’d prefer:

    “For a query that does not have an explicit ORDER BY clause, but that does have a GROUP BY clause, assume an implicit ORDER BY clause containing each GROUP BY expression that *explicitly* specifies ASC or DESC”

    In other words, sort according to GROUP BY only if the user asks for it

  6. fenixshadow Says:
    February 25th, 2008 at 4:47 pm

    Unbelievable.

  7. MySQL Pop Quiz #11 | Carsten’s Random Ramblings Says:
    March 4th, 2008 at 8:00 am

    [...] we saw in Pop Quiz #6, MySQL does offer one other method aside from ORDER BY. Do you have your own favorite MySQL [...]

  8. PHP Encoder Says:
    March 5th, 2008 at 11:01 pm

    Wow, very useful tips!

  9. Propiedad Privada » Blog Archive » Test rápidos de MySQL - Test 6 Says:
    March 24th, 2008 at 4:31 pm

    [...] Traducido con permiso expreso de Mysql Test Quiz 6 Original [...]

  10. dashin Says:
    March 28th, 2008 at 12:13 pm

    Hi!

    Would you have any objections if I make translation of your Quiz-series to russian in my blog (http://ru.andrewdashin.com)?

  11. Guerrero de Terracota » MySQL Pop Quiz #6 Says:
    March 31st, 2008 at 9:08 am

    [...] ???????????? ? ?????????? Carsten Pedersen. ???????? [...]

  12. Carsten Says:
    March 31st, 2008 at 8:30 pm

    @dashin: as per our agreement, please go ahead!

  13. zub Says:
    May 17th, 2008 at 2:58 pm

    i am developing a QUIZEngine , one of the requirement is that it has to shffle the order of question, when ever a button is clicked…………

    how can i do that … can anyone help ?

Comments