Saturday 23 September 2006 — This is 18 years old. Be careful.
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
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. :-)
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: