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

Discuss this article with the rest of the community on our Discord server!


Ready to dive into the realm of hands-on innovation? This collection serves as your passport to an exhilarating journey of cutting-edge tinkering and technological marvels, encompassing 15 indispensable books tailored for budding creators.