ExpatTech Techblog - MySQL

One day I just ran into the problem that in a MySQL query I want to give an ordinal row number to every resulting record. So I wanted a result something like:

1. Mocskarc
2. Retkarc
3. Kecsege

In a MySQL SELECT statement, you can use variables. You do this by putting a @ before the variable name (like @zsiguli). You can assign values using := like in good old Pascal. So my first version was something like this:

select @a:=@a+1 as rownum, name from sometable ...

For the first look, this seems allright, but using counters (@a) without initialization is not a good idea. So you can either run another command before this (@a:=0), which is not nice enough, or you can use a small trick. Subqueries in a SELECT are of course executed before the whole SELECT itself. So let's do this:

select @a:=@a+1 as rownum, name from (select @a:=0) as whatever, sometable ...

Enjoy.