MYSQL - generating subset sequence in a table

Question

Hi,

I have the following table

Id   Value
1     3
1     12
1     67
2     7
2     99
5     30
5     33
5     4
5     87
5     12
5     1

I'd like to update it to have this table.

Id  UniqueIdBySubSet    Value
1           1             3
1           2             12
1           3             67
2           1             7
2           2             99
5           1             30
5           2             33
5           3             4
5           4             87
5           5             12
5           6             1

I found the perfect thread on SO, but it was for mssql. I use mysql 4.1.10.

The other thread can be found here: http://stackoverflow.com/questions/492375/generating-sequences-for-subsets-of-a-table.

Does anyone knows how I can do that in mysql ?

Thank you, Jean-Francois

Answer 1

Write a quick script to do it. It should take less than 10 lines of code in php.

Answer 2

I think you'll find this article useful. In short: you can't do it in exactly one query, but you can do it in two (load your data into a new table and execute an insert statement).

Answer 3

SET  @r := 0;
SET  @id := 0;
UPDATE  mytable m
SET     m.UniqueIdBySubSet = IF(@id = id, @r := @r + 1, @r := (@id := id) - id)
ORDER BY
        id, value;