Selecting row number in MySQL

Michael Yakobi sent us this clever use of MySQL’s user defined variable syntax to return row numbers in a result set:

Occasionally, one wants to execute a query and have the rows in the results set numbered. This could be done using a variable. For example:

SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

I haven’t used this feature of MySQL before, but it looks like it could be pretty useful. The user defined variables are scoped to a connection but persist between statements, so you can use them to store intermediate state information between queries or even perform iterative calculation within a single query, as was done in the example above.

MySQL User-Defined Variables