1

I need to get a progressive number next on each row out of my SELECT query.

i.e. Query result without numbering:

user_id
paola
Carla
Marta

what i want is

number user_id
1      paola
2      Carla
3      Marta

I am aware that the problem is very similar to the one in

MySQL - Get row number on select

And on the first query the code suggested in the answers seems to work for me too BUT when I recall the query, or when i try to export the results, i get a NULL value in the column where the row number is supposed to be.

Do you know why? Did I do something wrong in the variable instance?

The code I use is the following:

set @x=0;

SELECT @x:=@x + 1 AS row_index, user_id FROM TABLE ORDER BY user_id
Community
  • 1
  • 1
Any
  • 75
  • 2
  • 9

2 Answers2

0

Your query looks correct. My guess is that you have a typo, so the variable being set is not the same as the variable being incremented.

By the way, these can be combined into a single statement:

SELECT @x:=@x + 1 AS row_index, user_id
FROM TABLE cross join
     (select @x := 0) const
ORDER BY user_id;

That is, the select can actually initialize the variable.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

try this

 SELECT @x:=@x + 1 AS row_index, user_id FROM 
      ( SELECT row_index , user_id from TABLE ORDER BY user_id ) t1 , 
      (SELECT @x:=0) t2;
echo_Me
  • 37,078
  • 5
  • 58
  • 78