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
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.