Let's say I have a table t with a column c which is an auto incrementing
primary key. I can retrieve the rows in t in a random order with the
statement

SELECT c FROM t ORDER BY RAND(0)

As long as t is unmodified I will get the rows in the same order each
time the query is run.

Now, when a new row is added to or deleted from the table I want the
"random" order of the existing rows to be preserved. For example if row
1, 2 and 3 is randomized as

(c_1), (c_2), (c_3) -> (c_3), (c_1), (c_2)

the following randomization would be valid after row 4 is inserted:

(c_1), (c_2), (c_3), (c_4) -> (c_3), (c_4), (c_1), (c_2)

In the select statement above, however, I reason that it will not
satisfy this condition. Is it possible to achieve this preserved random
order without post-processing the result? I think not.


/August

--
The competent programmer is fully aware of the limited size of his own
skull. He therefore approaches his task with full humility, and avoids
clever tricks like the plague. --Edsger Dijkstra

On 9/28/2010 1:08 PM, August Karlstrom wrote:
No. Any change to the table will change the output order. But in your
case RAND() is the wrong function to use because you don't really want a
random order.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2010-09-29 00:41, Jerry Stuckle wrote:
Well, I want a random order but with the mentioned restrictions. In the
example above the fourth row can be in positioned in one of four
possible positions. A random value will decide which one.


/August

--
The competent programmer is fully aware of the limited size of his own
skull. He therefore approaches his task with full humility, and avoids
clever tricks like the plague. --Edsger Dijkstra

On 9/28/2010 7:08 PM, August Karlstrom wrote:
Your restrictions preclude a "random order". You want a repeatable
order - which by definition is not random.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 28/09/10 19:08, August Karlstrom wrote:

- Handle it in code. I don't know what the problem is, but my gut
feeling says that is the most logical way to go if your problem involves
a "working set".

- Create a temporary table based on the SELECT statement. You won't get
a random insertion point, but you can solve that by adding an extra
field with a position value to the temporary table.

- Don't create a temporary table, but create a (unique) position column
in the original table. Upon insertion, fill it with a random number. If
you get a uniqueness violation, try again with a different random
position. Your SELECT query would then be modified to order by the new
position column. This is probably the best option if your problem is a
permanent randomization like in, for instance, medical trials.

Good luck,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On 2010-09-29 01:46, Jerry Stuckle wrote:
Call it what you want. Let me know, however, if something is unclear in
my specification.


/August

--
The competent programmer is fully aware of the limited size of his own
skull. He therefore approaches his task with full humility, and avoids
clever tricks like the plague. --Edsger Dijkstra

On 2010-09-29 09:57, Willem Bogaerts wrote:
Thanks for the input Willem. The thing is that since I cannot modify the
table or the database I have to store the position information
elsewhere. This is a table with some five millions rows so a serialized
binary tree with the positions as keys is thinkable.


/August

--
The competent programmer is fully aware of the limited size of his own
skull. He therefore approaches his task with full humility, and avoids
clever tricks like the plague. --Edsger Dijkstra

On 9/29/2010 5:14 AM, August Karlstrom wrote:
Your specification is clear. It is just incorrect. Come up with a
correct specification and we can help.

For instance, what happens if the algorithm changes in a new release and
RAND(0) now generates something else? Or maybe RAND(0) means generate a
new random seed each time? Either way, your current version will break.

Get away from the idea of a random arrangement. That is not what you
want. And if you can't change the tables to add a position indicator,
you're SOL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2010-09-29 13:08, Jerry Stuckle wrote:
Yes that's possible. In my case though, it would be acceptable as long
as it doesn't happen too often.

The repeatability is a part of the specified behaviour:

"If a constant integer argument N is specified, it is used as the seed
value, which produces a repeatable sequence of column values."

See
http://dev.mysql.com/doc/refman/5.0/...#function_rand

Yes, you are probably right.


/August

--
The competent programmer is fully aware of the limited size of his own
skull. He therefore approaches his task with full humility, and avoids
clever tricks like the plague. --Edsger Dijkstra

On 9/29/2010 11:55 AM, August Karlstrom wrote:
In current implementations, that is. But depending on a repeatable
sequence of random numbers is not good programming.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================