SQL trivia

Saturday 23 September 2006

I’ve been digging deeper into SQL details, especially for MySQL, and have found a few tidbits:

1. In a GROUP BY clause, you can use integers to indicate the column number to group by. I knew this was possible with ORDER BY, so it made sense that it would work for GROUP BY, but it never occurred to me to try it. These pairs of statements are equivalent:

select a, b from foo order by a, b;
select a, b from foo order by 1, 2;

select a, b, count(*) from foo group by a, b;
select a, b, count(*) from foo group by 1, 2;

2. MySQL provides a WITH ROLLUP modifier for grouped selects. This inserts sub-total and total rows in the appropriate places:

mysql> select color, count(*) from foo group by 1;
+-------+----------+
| color | count(*) |
+-------+----------+
| blue  |        6 |
| green |        6 |
| red   |        4 |
+-------+----------+
3 rows in set (0.00 sec)

mysql> select color, count(*) from foo group by 1 with rollup;
+-------+----------+
| color | count(*) |
+-------+----------+
| blue  |        6 |
| green |        6 |
| red   |        4 |
| NULL  |       16 |
+-------+----------+
4 rows in set (0.00 sec)

3. At least according to these thorough and authoritative-looking SQL grammars, the LIMIT clause is not a part of any SQL standard!

Comments

[gravatar]
Malcolm Tredinnick 9:12 AM on 23 Sep 2006

You're right about LIMIT (and OFFSET). They aren't part of the spec. I believe (very sketchy memories in action here) their heritage lies in a very early version of msql (it may already have become mySQL). Rasmus Lerdorf (Mr. PHP) wrote the initial implementation.

From a pure-SQL, they're evil because SQL talks about sets, not ordered collections. However, ORDER BY is in SQL, so that purity is out the window. And when you see the hoops other systems jump through (SQL Server is pretty spectacularly obtuse) to get the same effect, don't be ashamed to use LIMIT and OFFSET where they exist. :-)

[gravatar]
Fredrik 11:07 AM on 23 Sep 2006

Isn't the spec's intent that things like LIMIT and OFFSET should be handled by a cursor level API, rather than by the query itself? According to the spec, a "cursor in the open state" consists of three things: 1) a table (defined by the query), 2) an ordering of the rows of that table, and 3) a position relative to that ordering (where the ordering may be implementation dependent, if no ORDER BY clauses are used).

[gravatar]
Andy Todd 5:37 PM on 24 Sep 2006

I'm with Fredrik. The addition of ORDER BY to the standard was a bit of an oversight, it was probably slipped in the morning after a big standards dinner.

Of course, the cursor API can be interpreted different ways. Some databases (and I'm pointing the finger at Oracle here) provide the ability to scroll forwards through a cursor result set but not backwards. This means that if you want to implement backwards scrolling in your application you have to keep a copy of every row you retrieve in memory. Which is nice.

Add a comment:

Ignore this:
Leave this empty:
Name is required. Either email or web are required. Email won't be displayed and I won't spam you. Your web site won't be indexed by search engines.
Don't put anything here:
Leave this empty:
URLs auto-link and some tags are allowed: <a><b><i><p><br><pre>.