As everyone has mentioned - you should always assume the data comes back randomly - even
if the table is completely static and there have been no inserts or updates, but it's
even more subtle than that.
When you port your application to a database than allows your queries to run
multi-threaded (Oracle, DB2, SQL Server) - every query process will return a chunk of the
data and one process will mastermind and glue the chunks together. There is no guarantee
that the chunks will always be glued in the same order - unless ORDER BY has been
specified.
People who get sloppy in single threaded databases and only use a GROUP BY without an
ORDER BY, relying on the fact that the GROUP BY does a sort and makes the ORDER BY
unnecessary - get a rude awakening when they move to a multi-threaded database where
every query process returns its chunk sorted but the different chunks are glued out of
sequence unless you explicitly specified the ORDER BY.
So - certain errors will never become apparent in single-threaded MySQL or Microsot
Access, but will sting you when upgrading to a multi-threaded database. Therefore -
beware - and always specify ORDER BY if you want your data sorted.
In a message dated 5/3/2004 1:39:48 PM Eastern Daylight Time, bhemphill@stripped writes:
>
> 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?
>
> Best Regards,
> Boyd E. Hemphill
> bhemphill@stripped
> Triand, Inc.
> www.triand.com
> O: (512) 248-2287
> M: (713) 252-4688
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
>
>
