CODEWITHSUNDEEP
mysqlalphabetical
cardflopper
cardflopper

Reputation: 1056

MYSQL: how to "reorder" a table

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?

Upvotes: 8

Views: 17120

Answers (6)

Tim Chaubet
Tim Chaubet

Reputation: 726

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.

Upvotes: 0

Yada
Yada

Reputation: 31225

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;

Upvotes: 15

JohnFx
JohnFx

Reputation: 34909

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.

Upvotes: 10

Paul Creasey
Paul Creasey

Reputation: 28834

 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.

Upvotes: -1

Eric J.
Eric J.

Reputation: 150108

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.

Upvotes: 7

ademers
ademers

Reputation: 967

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.

Upvotes: 2

Related Questions