Bob Ramsey wrote:
> Ah, but the ordering is not random. As your example has it, the results
> are in the order that the entries were inserted into the table. There
> is an explanation for the order of the returned data.
<snip>
Apparently not random, but not in the order inserted either. Consider:
create temporary table foo (num int(10));
insert into foo values (1), (2), (3), (4), (5);
select * from foo;
delete from foo where num = 3;
insert into foo values (6);
insert into foo values (3);
mysql> select * from foo;
+------+
| num |
+------+
| 1 |
| 2 |
| 6 |
| 4 |
| 5 |
| 3 |
+------+
6 rows in set (0.01 sec)
(Same example as before with the "delete...where num=6" removed.) Note the
6 is where the 3 was originally, because the slot where the first 3 was
inserted/deleted was reused for the 6.
This trivial example yields results which are ordered neither by num nor by
the order inserted. The lesson is clear: The *only* way to be sure your
rows are sorted in a particular way is to explicitly request it with an
ORDER BY clause, as several others have pointed out. This is really a
fundamental principle: It is the data in the row that matters, not how or
where it is stored.
Michael
