Reputation: 3028
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
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