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

juergen d · Accepted Answer · 2018-03-15T15:52:21.090

127

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.

edited Mar 15 '18 at 15:52

answered Nov 26 '12 at 14:19

juergen d

195,137
36
275
343

3

As Fabio Reche was trying to say, we need to write it with a '@' in front of `rownum + 1`...isn't it suppose to be `@rownum := @rownum + 1` ? – ghiscoding Jan 17 '14 at 19:42
1

Thanks, +1 for `CROSS JOIN`, solved anther problem when I need group by clausule (map/reduce) – Ragen Dazs Jun 24 '16 at 20:44
The subrequest is used to intializing @rownum. – Genjo Aug 04 '16 at 15:34
This solution doesn't work properly if you sort values. Counter increments for unsorted rows. – Paktas Oct 02 '17 at 10:34
@Paktas: I have no idea what are you talking about. Could you elaborate? – juergen d Oct 02 '17 at 12:24
Reading & writing the same variable in the same select statement is undefined behaviour in MySQL. – philipxy Aug 28 '19 at 23:21
@philipxy: You got a source for that? – juergen d Aug 29 '19 at 06:09
Read the manual sections pre-8.0 on user variables & assignment. 8,0 explicitly deprecates it. Also google my SO comments re this (with & without 'percona'). But if you don't have an authoratative source saying it's ok, you shouldn't be saying that it is. – philipxy Aug 29 '19 at 19:47

score 17 · Answer 2 · edited Aug 29 '19 at 06:11

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

score 11 · Answer 3 · edited Nov 23 '19 at 01:42

11

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.

edited Nov 23 '19 at 01:42

Selaka Nanayakkara

2,258
16
34

answered Nov 22 '19 at 16:41

Nili Waypa

111
1
5

2

Very interesting. Could you explain this syntax? This solved a problem I was having with a counter over grouped items where the results were not consistent. At the console int he same session, the first run returned all 1s then from the second run it worked okay. So it never worked as a script. Until I removed the initial set @var commands and used your method. Now it works consistently! – Chris Njuguna Jul 08 '20 at 10:50
@ChrisNjuguna: The syntax is the old legacy SQL join syntax where you comma separate the tables you want to join. – juergen d Apr 30 '22 at 13:12

score 2 · Answer 4 · answered Jan 28 '21 at 22:14

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

4 Answers4

Linked