ExpatTech is closed for the holidays from December 22nd to January 1st. We will open again on Wednesday January the 2nd. Happy Holidays!

ExpatTech Techblog - MySQL

Nagy Richárd 2008.10.28. 19:28

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:

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.