I have this table:
NAME POSITION
a1 567
a2 456
a3 31
...
a134 90
...
a183 4
aa1a1 78
...
b1 8
b2 67
...
b145 3
...
bccb18 45
the value in POSITION should match their order when sorted alphabetically (just like on Windows) like this:
NAME POSITION
a1 1
a2 2
a3 3
...
a134 134
...
a183 183
aa1a1 184
...
b1 1156
b2 1157
...
b145 1201
...
bccb18 1395
I tried this:
SET @i = 0;
UPDATE MyTable
SET POSITION = (@i := @i + 1)
ORDER BY name ASC, name ASC;
I get this:
NAME POSITION
a1 1
a10 2
a100 3
...
How can I get the values sorted out in the NATURAL order? A php solution would be accepted as well.
P.S. this question has NO ANSWER yet so dont link to other questions suggesting there is the answer please.