Mysql Auto increment value in select grouped by Id

Question

I have 3 MySql tables and I need to use some information to populate a new table.

SELECT table1.products_id as 'ID',
  concat(table2.table2_name,':',table2.table2_name,':0') as 'att',
  concat(table3.table3_name,':',(@cnt := @cnt + 1)) as 'val' 
FROM table1, table2, table3 
CROSS JOIN (SELECT @cnt := 0) AS dummy 
WHERE table1.options_id=table2.table2_id 
  AND table1.options_values_id=table3.table3_id 
  AND table2.language_id=4 AND table3.language_id=4

Using this query I obtain all these (correct) informations

+++++++++++++++++++++++++++++++++++++
+ ID  +   att            +  val     +
+++++++++++++++++++++++++++++++++++++
+ 22  +  Taglia:Taglia:0 +  S:1     +
+ 22  +  Taglia:Taglia:0 +  M:2     +
+ 22  +  Taglia:Taglia:0 +  L:3     +
+ 55  +  Taglia:Taglia:0 +  S:4     +
+ 55  +  Taglia:Taglia:0 +  M:5     +
+ 60  +  Taglia:Taglia:0 +  1:6     +
+ 60  +  Taglia:Taglia:0 +  2:7     +
+ 60  +  Taglia:Taglia:0 +  3:8     +
+ 62  +  Taglia:Taglia:0 +  8,5:9   +
+++++++++++++++++++++++++++++++++++++

but I need that the autoincrement value restart when ID change, like this:

+++++++++++++++++++++++++++++++++++++
+ ID  +   att            +  val     +
+++++++++++++++++++++++++++++++++++++
+ 22  +  Taglia:Taglia:0 +  S:1     +
+ 22  +  Taglia:Taglia:0 +  M:2     +
+ 22  +  Taglia:Taglia:0 +  L:3     +
+ 55  +  Taglia:Taglia:0 +  S:1     +
+ 55  +  Taglia:Taglia:0 +  M:2     +
+ 60  +  Taglia:Taglia:0 +  1:1     +
+ 60  +  Taglia:Taglia:0 +  2:2     +
+ 60  +  Taglia:Taglia:0 +  3:3     +
+ 62  +  Taglia:Taglia:0 +  8,5:1   +
+++++++++++++++++++++++++++++++++++++

How can I do this ?

Partially solved... @Shadow comment guide me in the right way, but I'm doing something wrong :(

NEW QUERY

    SET @num := 0, @type := '';
SELECT *
    FROM ( select table1.products_id as id, concat(table2.table2_name,':',table2.table2_name,':0') as 'attributo',concat(table3.table3_name,':',@num) as 'valore'  FROM table1, table2, table3 
    WHERE table1.options_id=table2.table2_id AND table1.options_values_id=table3.table3_id AND table2.language_id=4 AND table3.language_id=4  
    ORDER BY `table1`.`products_id` ASC) as table_name2
    WHERE 0 <= GREATEST(
       @num := IF(@type = id, @num + 1, 1),
       LEAST(0, LENGTH(@type := id)))

NEW RESULT

+++++++++++++++++++++++++++++++++++++
+ ID  +   att            +  val     +
+++++++++++++++++++++++++++++++++++++
+ 22  +  Taglia:Taglia:0 +  S:2     +
+ 22  +  Taglia:Taglia:0 +  M:4     +
+ 22  +  Taglia:Taglia:0 +  L:6     +
+ 55  +  Taglia:Taglia:0 +  S:2     +
+ 55  +  Taglia:Taglia:0 +  M:4     +
+ 60  +  Taglia:Taglia:0 +  1:2     +
+ 60  +  Taglia:Taglia:0 +  2:4     +
+ 60  +  Taglia:Taglia:0 +  3:6     +
+ 62  +  Taglia:Taglia:0 +  8,5:2   +
+++++++++++++++++++++++++++++++++++++

Where I'm wrong?

I use it as to order value. These are attribute for product... so products ID 22 has an attribute "taglia" (size) with these values S M L and I want to mantain the order, so S 1° - M 2° - L 3° etc ... — Saxcbr, Mar 14 '17 at 16:19
Ok, why would it have to restart? It just increases in value as time goes by, what does it matter to restart it then? You don't *restart* auto_increment, its job is only to uniquely identify a record. Nothing else. It's not made to be sequential, it's not made to be used as pretty number thingy, it's not made to be used for anything except to uniquely identify a row. If you believe you need to somehow alter it, you can be 100% sure you are doing something wrong. — Mjh, Mar 14 '17 at 16:20
It's a migration from 2 shop and the new one need information in these way — Saxcbr, Mar 14 '17 at 16:22
Doesn't make it any less wrong. You tamper with `auto_increment`, you pay the price. — Mjh, Mar 14 '17 at 16:23
@Mjh it is not really resetting any auto increment, check out the query. — Shadow, Mar 14 '17 at 16:30
@Mjh It's a fake auto_increment, it's not the auto_increment of the table. I need it to only give order to the options values of a product (ID) before use these fields in a "INSERT INTO new_table select from .... " — Saxcbr, Mar 14 '17 at 16:33
Possible duplicate of [How might sub-groups have a generated increment column added in an sql query?](http://stackoverflow.com/questions/19144189/how-might-sub-groups-have-a-generated-increment-column-added-in-an-sql-query) — Shadow, Mar 14 '17 at 16:33

Mysql Auto increment value in select grouped by Id

0 Answers0