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.
ADVERTISEMENT
