By Carsten | February 21, 2008
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?
Answer: The ORDER BY clause is not needed. In MySQL, when using GROUP BY, the rows are sorted according to the GROUP BY columns just as if you had an ORDER BY for the same columns. In fact, you must explicitly specify ORDER BY NULL to prevent the sorting from occurring. Also, you may add ASC or DESC directly to the GROUP BY clause to control the sorting that takes place. The full story here.
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.