Ordinal row numbers in MySQL queries
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:
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 ...