Search code examples
mysqldatabaseselectcounter

MySQL SELECT increment counter

Here is my MySQL query:

SELECT name FROM table;

How can I also select an increment counter alongside name? Expected output:

Jay 1
roy 2
ravi 3
ram 4
Solution 
  • 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 introduce a variable without the need of a seperate query. So the first query could also be broken down into two 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.