On Mon, 2004-05-03 at 11:21, 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.
I don't think the point of the original question was "Are returned results random and completely unpredictable", but "Can I rely on the order of unordered results to be the same every time". The test I provided proves that no, they cannot be relied upon to be same every time. Anyway, if result order isn't the same every time, but in an arbitrary database specific order, they might as well be random. > bob > At 12:55 PM 5/3/2004, Garth Webb wrote: > >On Mon, 2004-05-03 at 10:39, Boyd E. Hemphill wrote: > > > My boss says that if you do a select statement against a table the > > > result set always comes back in the same order. I say that this is a > > > myth and that the result is random, except when some ordering is > > > specified in the SQL statement. > > > > > > Who is right? Is this behavior specified by ANSI or ISO? > > > >You are correct. Ordering takes time. Why choose a random column on > >which to order the results and take additional time when the user didn't > >ask for it. Here's the proof: > > > >create temporary table foo (num int(10)); > >insert into foo values (1), (2), (3), (4), (5); > >select * from foo; > > > >+------+ > >| num | > >+------+ > >| 1 | > >| 2 | > >| 3 | > >| 4 | > >| 5 | > >+------+ > >5 rows in set (0.00 sec) > > > >delete from foo where num = 3; > >insert into foo values (6); > >insert into foo values (3); > >delete from foo where num = 6; > >select * from foo; > >+------+ > >| num | > >+------+ > >| 1 | > >| 2 | > >| 4 | > >| 5 | > >| 3 | > >+------+ > >5 rows in set (0.00 sec) > > > > > >Garth > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > ====================================================================== > Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III > ph: 1(319)335-9956 187 Boyd Law Building > fax: 1(319)335-9019 University of Iowa College of Law > mailto:[EMAIL PROTECTED] Iowa City, IA 52242-1113 > For Hardware and Software questions, call 5-9124 > ====================================================================== > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
