MySQL SELECT increment counter

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

Has anyone ever face this case, where I query

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

the result is

roy  4
ravi 3
ram  2
jay  1

but what I want is

roy  1
ravi 2
ram  3
jay  4

when I query in mariadb version 10.5.9 the result is like what I want, but when I trial in mariabd version 10.6.10 or 10.9.2 , the result not I expected.