CODEWITHSUNDEEP
mysql
Krøllebølle
Krøllebølle

Reputation: 3028

Reorder MySQL rows: move row "up"

I have a MySQL table where rows are inserted in a given order by the user. If the user then forgets to add a row and inserts it later it will come after the others that are already inserted, also when performing simple query. The order of insertion is the order I want the elements to be retreived. The table in question is a simple table resolving a many-to-many relationship with two IDs (recipe_id and ingredient_id).

mysql> select * from ingredient_in_recipe where recipe_id = 7;
+-----------+---------------+------+----------+------------+
| recipe_id | ingredient_id | unit | quantity | group_name |
+-----------+---------------+------+----------+------------+
|         7 |            71 | g    |   300.00 | NULL       |
|         7 |            34 | stk  |     3.00 | NULL       |
|         7 |            72 | stk  |     1.00 | NULL       |
|         7 |            73 | stk  |     0.50 | NULL       |
|         7 |            45 | stk  |     6.00 | NULL       |
|         7 |            74 | stk  |     0.50 | NULL       |
|         7 |            23 | g    |    15.00 | NULL       |
|         7 |            78 | ts   |     2.00 | NULL       |
|         7 |            75 | ts   |     3.00 | NULL       | <--- This is where I want the last element to be.
|         7 |            76 | ss   |     1.00 | NULL       |
|         7 |            77 | stk  |     1.00 | NULL       |
|         7 |            79 | g    |   195.00 | NULL       |
|         7 |            38 | ss   |     5.00 | NULL       | <--- This is inserted later. Should be "higher up".
+-----------+---------------+------+----------+------------+
13 rows in set (0.00 sec)

Is there a simple way to achieve this?

Upvotes: 0

Views: 2801

Answers (1)

Kr&#248;lleb&#248;lle
Kr&#248;lleb&#248;lle

Reputation: 3028

As pointed out by @C4ud3x and @Hanno Binder, there is no guarantee that MySQL returns the data in the same order as they were inserted. Normally, this is the case, but it is not a robust way to handle ordering of the data. Thus I solved the problem by adding a column order_id to be able to use the ORDER BY clause to ensure that the order will be maintained properly.

I found a good answer to a related question over at dba.stackexchange.com. The main point from the accepted answer there is:

If, on the other hand, you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

Upvotes: 1

Related Questions