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
[EMAIL PROTECTED]
Triand, Inc.
www.triand.com
O:  (512) 248-2287
M:  (713) 252-4688
-----Original Message-----
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 03, 2004 12:57 PM
To: Bob Ramsey
Cc: [EMAIL PROTECTED]
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 ([EMAIL PROTECTED])
 / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/  /_/\_, /___/\___\_\___/ Stockholm
       <___/   www.mysql.com Cellphone: +46 708 608121
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]