Say you have a table of names that you want to alphabetize.  Of course you could SELECT and ORDER BY name ASC.  Lets assume that’s not an option and you want to save their alphabetical order in a special column so then you can ORDER BY this column.  This might be useful if you have an application where users can manually order their items but you give them them option to reset to alphabetical.  Here’s what your table looks like:

people
- id (int)
- name (varchar)
- sort (int)

Here’s how you can populate that sort column with integers that, when ordered by, put the results in alphabetical order:

SET @i=0;
UPDATE people
INNER JOIN (
SELECT id, @i:=@i+1 AS i FROM people ORDER BY name ASC
) counter ON counter.id = people.id
SET people.sort = counter.i;

The SET command initializes a variable.  The subquery orders the list and adds a new column (i) that counts off each row effectively.  Then the outer query joins on this by the PRIMARY id so that we match the ordered row from the subquery with the outer query.  And lastly, we store the i value back in the table.

I’ve never understood (well, I’ve never looked into) why MySQL defaults to that swedish collation.  If your tables are a mix of collations, here is a nice php script that will convert all the tables to the collation of your choosing.  It even has a nice GUI you use in the web browser.  Download the script here (courtesy of phoca.cz).