MYSQL: how to "reorder" a table

Question

I have a table like the following,

| id  | name   | color  |
------+--------+---------
| 1   | pear   | green  |
| 2   | apple  | red    |
| 3   | banana | yellow |
| 4   | grape  | purple |

I'd like to reorder alphabetically using the "name" column and reset the id (autoincrement) with this new order to end up with the following

| id  | name   | color  |
------+--------+---------
| 1   | apple  | red    |
| 2   | banana | yellow |
| 3   | grape  | purple |
| 4   | pear   | green  |

QUESTION: how can I do this with MYSQL?

Jori

Why do you want the id order to match the name order? – Wickerwork

Answer 1

Can I ask why you would want to do this?

If anyone modifies any of the name values or inserts new rows it will mess up your ordering scheme. Trying to store some meaning in the ordering of the PK that is already available elsewhere in the table (the name column) seems redundant and consequently a bad idea.

A much better solution is not to worry about the value of the ID column and just sort on the name column when you use the data in your app.

PS: Sorry for the non-answer type response. Normally I'd assume you had a good reason and just give an answer that directly addresses what you are trying to do, but I noticed from your other questions that you are still in the early learning stages about database design, so I wanted to help point you in the right direction instead of helping further your progress towards an ill-advised approach.

Bala

That is a good point. If you need some unique number that corresponds to the sorted value of each string, you can add an extra column and update as needed. Reordering the ID will break things badly if the ID is used as a foreign key in another table. – Empoverish

The only time I'd suggest adding an extra column for sorting would be when the sort can't be determined by other columns in the table. For example, if the application allowed users to re-order the items arbitrarily. – Bala

The records form a set. The sets aren't supposed to be ordered into a specific order. Recall that a relational database theory is based on pure mathematical notions. If you want a specific order in your application, add an orderby clause, otherwise the execution plan will decide how to retrieve the data, and hence the order; these plans can change over time. Moreover, recall that surrogate keys are meaningless to the enduser. – Darlenadarlene

If you add an additional column or keep just the id column make sure you add corresponding indexes for those columns, otherwise after the table gets too big wou wont be able to search it efficiently. – Dur

Answer 2

The cleanest way to reset the auto increment is to create another table.

MySQL provides commands such as CREATE TABLE LIKE and RENAME TABLE that are useful.

CREATE TABLE table2 LIKE table1;

INSERT INTO table2
  SELECT * FROM table1 ORDER BY name;

DROP TABLE table1;

RENAME TABLE table2 TO table1;

Kee

That's wrong. You cannot select all (SELECT * FROM table1 ), because if you do so, you will copy the PK as well with the same association that it has with the row information. Which will leave you with the same exact table having the same name and color linked to the same id, but with a different ordering. – Volny

one correction, you will need to drop PK from table 2 before you insert from table1; then dont do select * , yet select all columns except for ID. and it will work like charm – Abstraction

Answer 3

You can SELECT INTO a new table from the old table, ordering your select into as desired. Have an auto-increment ID in the new table. If needed, drop the old table and rename the new table.

Empoverish

Answer 4

Why not adding "ORDER BY name ASC" at the end of your query? My guess would be that you need the ID for some reason.

Neman

Answer 5

If you have a table with an autoincrement primary key (named 'id' for example), and the key is not being depended on by other tables, the way to go about this is this.

1. Remove the column id entirely.
2. alter table order by column_x, column_y;
3. Add the primary key column 'id' again, with autoincrement.

I did this a few times, with success and quite fast, using phpmyadmin. Reordering a table that has a primary key as an index, is impossible. That's why u need to remove it first.

If u need to keep the 'id' column, but need to re-sort, based on other columns, u need to omit the primary key & index status of the id column & re-sort. Then you need to add a new column as primary key / index.

Tiberius

Answer 6

 SELECT
       RANK() Over (ORDER BY Name) As NewID
     , Name
     , Color

 FROM Fruits

could save to a temp table then truncate then truncate the fruit table and insert, but it's probably a crappy solutions.

Gangboard

MySQL doesn't support windowed functions. – Caressive

this very close, but how to now save the NewID to the id ? – Hangover