MySQL SELECT increment counter

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.

In MySQL 8 and above you can also use the ROW_NUMBER() Window function.

SELECT
    name,
    ROW_NUMBER() OVER ()
FROM table

Result:

Jay  1
roy  2
ravi 3
ram  4

As shown by juergen d, it would be a good idea to put an ORDER BY to have a deterministic query.

The ORDER BY can apply to the query and the counter independently. So:

SELECT
    name,
    ROW_NUMBER() OVER (ORDER BY name DESC)
FROM table
ORDER BY name

would give you a counter in decreasing order.

Result:

Jay  4
ram  3
ravi 2
roy  1

SELECT name,
      @rownum := @rownum + 1 as row_number
FROM your_table
   ,
   (select @rownum := 0) r

I prefer using a comma instead of CROSS JOIN as it performs faster. Using CROSS JOIN will add one extra step of adding a column to your table.

Solutions with cross join and comma won't work if your query has GROUP BY statement. For such cases you can use subselect:

SELECT (@row_number := @row_number + 1) AS rowNumber, res.*
FROM
(
  SELECT SUM(r.amount) 
  FROM Results r 
  WHERE username = 1 
  GROUP BY r.amount
) res
CROSS JOIN (SELECT @row_number := 0) AS dummy

MySQL SELECT increment counter

Related

Recent Posts