List:General Discussion« Previous MessageNext Message »
From:Garth Webb Date:May 3 2004 5:55pm
Subject:Re: urban myth?
View as plain text  
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
Thread
Create table results in (errno: 121)Tom Brown22 Apr
  • Re: Create table results in (errno: 121)Martijn Tonies22 Apr
  • Re: Create table results in (errno: 121)Tom Brown22 Apr
  • Re: Create table results in (errno: 121)Adam28 Apr
    • urban myth?Boyd E. Hemphill3 May
      • Re: urban myth?Peter J Milanese3 May
      • Re: urban myth?Daniel Clark3 May
      • Re: urban myth?Garth Webb3 May
        • Re: urban myth?Bob Ramsey3 May
          • Re: urban myth?Josh Trutwin3 May
          • Re: urban myth?Garth Webb3 May
          • Re: urban myth?Michael Stassen3 May
            • RE: urban myth?Boyd E. Hemphill3 May
              • Re: urban myth?Anders Karlsson3 May
              • Re: urban myth?Michael T. Babcock13 May
      • Re: urban myth?Josh Trutwin3 May
      • Re: urban myth?Jeremy Zawodny3 May
      • Re: urban myth?gerald_clark3 May
        • Re: urban myth?Peter J Milanese3 May
Re: Create table results in (errno: 121)beacker22 Apr
RE: urban myth?Mike Johnson3 May
Re: urban myth?Udikarni3 May