MySQL GROUP_CONCAT Assign incrementing value

Question

I need to assign an incrementing value to each string from the concatenated row starting at 0 for each row in the result.

Here's a simplified data sample:

number|color
1     |red
1     |blue
1     |orange
2     |brown
3     |purple
3     |yellow

This is the result that I need:

number|color_set
1     |0 red,1 blue,2 orange
2     |0 brown
3     |0 purple,1 yellow

This is the result that I get:

number|color_set
1     |0 red,1 blue,2 orange
2     |3 brown
3     |4 purple,5 yellow

This is what I've been trying:

SET @x:=0;

SELECT number, GROUP_CONCAT(@x:=@x+1,' ',color SEPARATOR ',')
FROM table
GROUP BY number;

The variable needs to be reset to 0 for each result row. I'm no expert obviously and I'm new to the site so any help is much appreciated.

Thanks,

Jay

Answer 1

Just for fun...

 CREATE TABLE colors(color_set INT NOT NULL,color VARCHAR(12) NOT NULL,PRIMARY KEY (color_set,color));

 INSERT INTO colors VALUES
 (1     ,'red'),
 (1     ,'blue'),
 (1     ,'orange'),
 (2     ,'brown'),
 (3     ,'purple'),
 (3     ,'yellow');


      SELECT color_set
      , GROUP_CONCAT(CONCAT_WS(' ',n,color) ORDER BY n) array
   FROM
      ( SELECT c.*
             , FIND_IN_SET(color,x)-1 n 
          FROM colors c
          JOIN 
             ( SELECT color_set
                    , GROUP_CONCAT(color ORDER BY LENGTH(color),color) x 
                 FROM colors 
                GROUP 
                   BY color_set
             ) y
            ON y.color_set = c.color_set
      ) z
  GROUP 
     BY color_set;

 +-----------+-----------------------+
 | color_set | array                 |
 +-----------+-----------------------+
 |         1 | 0 red,1 blue,2 orange |
 |         2 | 0 brown               |
 |         3 | 0 purple,1 yellow     |
 +-----------+-----------------------+