List:General Discussion« Previous MessageNext Message »
From:Josh Trutwin Date:May 3 2004 5:57pm
Subject:Re: urban myth?
View as plain text  
On Mon, 3 May 2004 12:39:48 -0500
"Boyd E. Hemphill" <bhemphill@stripped> 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?

I believe that the relation database model specifies that the order of rows is not
important in the resulting relation produced from a relational operation (Select,
Project, Union, Join, etc.).  Whether or not you actually get the same order on the same
SELECT query run multiple times depends on the DBMS you are using.  I think different
DBMS's may do things in idle time to optimize table layout and perhaps re-order the data,
I'm not sure about MySQL.  Conceptually as an end-user, you should never assume that the
DBMS will always return your data in the same order regardless of how/where it is
physically stored, that's the job of the ORDER BY clause in a SELECT query.  The DBMS is
supposed to abstract away the details of the physical storage implementation, so when
order is important, every query should have an ORDER BY to guaruntee the sort order,
especially if you want to port the application.

Josh
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