Re: urban myth?

[Anders Karlsson]

Also, I'd say that it depends on what you mean by "row". If you mean the 
same physical data as
was previously displayed, then they probably WILL come back in the same 
order, but there
are no guarantees (a dataset is always unordered, unless something else 
is specified).

But on the other hand, if by row, we mean "the same unique data", well 
then it might change as someone
else might delete a row, and then someone else again might insert the 
same unique data. Same data, but
a different physical row. In this case, data will certainly come back in 
a different order, and if you only
look at, say, the unique identifier to determine row position, then the 
row might well have changed it's
position within the row, fact is, it is much more likely that it has!

Finally, a quote from Chris Date, as read in "Relatuional databases - 
Selected writings", rom the
section entitled "Relational Database: An overview": "If the entire 
ORDER BY clause is omitted, the result appears in
unpredictable order" and "A table is an unordered set of rows". And as 
the result of a SELECT is
also considered a relation (or a table), this latter quote applies too.

/Karlsson
Boyd E. Hemphill wrote:

>To all who answered thank you.  This answer below is the one that I can
>use to convince him what he proposes is not necessarily safe.  
>
>Now I just need to decide how to convince him it was his idea :-)
>
>
>
>Best Regards,
>Boyd E. Hemphill
>bhemphill@stripped
>Triand, Inc.
>www.triand.com
>O:  [phone]
>M:  [phone]
>
>-----Original Message-----
>From: Michael Stassen [mailto:Michael.Stassen@stripped] 
>Sent: Monday, May 03, 2004 12:57 PM
>To: Bob Ramsey
>Cc: mysql@stripped
>Subject: Re: urban myth?
>
>
>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
>
>
>
>  
>

-- 
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@stripped)
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
 /_/  /_/\_, /___/\___\_\___/ Stockholm
        <___/   www.mysql.com Cellphone: [phone]