views:

445

answers:

5

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?

+2  A: 

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.

AlexDemers
+5  A: 

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.

Eric J.
+6  A: 

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.

JohnFx
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.
Eric J.
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.
JohnFx
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.
lmsasu
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.
Geek Num 88
A: 
 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.

Paul Creasey
A: 

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 by name;

DROP TABLE table1;

RENAME TABLE table2 TO table1;
Yada