Mysql – theSQL – Ordering table as desired, before Adding Primary Key column

mysql

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??

Best Solution

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.

Related Question