Assigning row rank numbers

I have a database. I want to update a column of it. The column should contain unique integer numbers in ascending order according to alphabetical order of another column.

Sorry not clear maybe, I want to have integer numbe开发者_如何学Crs like this:

1 ACC 501
2 BCC 501
3 GCC 601
4 FCC 601

Is there a reasonably simple way of setting this rank/order with mysql or sql query?

---

What you need is a ranking function which is not supported by MySQL at the moment. However, you can simulate them like so:

Set @rownum := 0;

Select rnk, SomeCode, SomeNum
From    (
        Select @rownum := @rownum + 1 As rnk, SomeCode, SomeNum
        From MyTable
        Order By SomeCode Asc
        ) As Z

---

Create another table that has the same schema as your original table, plus the new column. The new column should be an autonumber. Do an INSERT...SELECT into that table. The new column will be filled out with the values you want.

---

Like what Alex said, you want to create a new table like

CREATE TABLE newTable(
#Table definition from current table,
id INT NOT NULL AUTO_INCREMENT
);

And then insert with

INSERT INTO newTable
SELECT * FROM oldTable
ORDER BY orderColumn;

I think you can quickly do the create table with

CREATE TABLE newTable LIKE oldTable;
ALTER TABLE newTable ADD COLUMN id INT NOT NULL AUTO_INCREMENT;