iJade iJade - 14 days ago 7
MySQL Question

select increment counter in mysql

Here is my mysql query

select name from table;


I want to select an increment counter along side name.How to do this.
so the expected output will be

Jay 1
roy 2
ravi 3
ram 4

Answer
select name,
      @rownum := @rownum + 1 as row_number
from your_table
cross join (select @rownum := 0) r
order by name

This part:

cross join (select @rownum := 0) r

makes it possible to init a variable without the need of a seperate query. So the first query can be also made with 2 different queries like this:

set @rownum := 0;

select name,
      @rownum := @rownum + 1 as row_number
from your_table
order by name

for instance when used in a stored procedure.