mySQL - Ordering table as desired, before Adding Primary Key column

Question

Am using the following notation to add a primary key to a table:

ALTER TABLE tablename ADD id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

And this works fine, but it seems to default the order of the table to the original input order of the rows, before adding this primary key. And this is not ideal for the current situation.

The order of this table is important, as it represents a menu structure, and I need the table to be ordered by parentId, as follows, before the primary key is added to the table:

+------+----------+---------------------------
| id | parentId | ...
+------+----------+---------------------------
| 1 | 1 | ...
+------+----------+---------------------------
| 2 | 1 | ...
+------+----------+---------------------------
| 3 | 2 | ...
+------+----------+---------------------------
| 4 | 2 | ...
+------+----------+---------------------------
| 5 | 2 | ...
+------+----------+---------------------------
. . .

This is how the table should look after the adding of the primary key column "id", but currently I can not seem to order the table by parentId before the id column is added.

I have been trying variations of the above query such as:

ALTER TABLE tablename ADD id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ORDER BY parentId ASC

but with no success.

Any suggestions??

Answer 1

The AUTO_INCREMENT column shouldn't necessarily conform to any particular order — it's just arbitrary integers. What will happen down the road, for example, when you add some new item to the menu? It will "belong" in the middle of the table, but its AUTO_INCREMENT value will be a newly assigned value, not subject to your current ordering.

Instead of worrying about the order of the AUTO_INCREMENT values, simply apply the correct ORDER BY clause when selecting data from your table:

SELECT *
FROM tablename
ORDER BY parentId ASC;

Even with an AUTO_INCREMENT column you have to apply an ORDER BY clause to ensure the order of results, so you'd might as well use the one that makes the most semantic sense.

Answer 2

One way would be to create a new table with the primary key, then do an INSERT INTO newTable SELECT * FROM oldTable ORDER BY parentID. Then you can drop the old table, and rename the newTable.

Answer 3

Ordering by ParentId might not work since you have duplicates in that column - for example how would it know which of rows 1 and 2 to sort by?

Could you add another column, SortOrder and order by that?