3 added 287 characters in body
source | link

All you have to do is set whichever row is the cut off point ONCE. You may want to possibly store the cutoffpoint in a MEMORY table, fetch it, increment it, are store it back in the MEMORY table. Or you may want to only increment the cutoffpoint whenever you want to allow multuiple sessions to see the same display until you increment the cutoffpoint.

All you have to do is set whichever row is the cut off point ONCE.

All you have to do is set whichever row is the cut off point ONCE. You may want to possibly store the cutoffpoint in a MEMORY table, fetch it, increment it, are store it back in the MEMORY table. Or you may want to only increment the cutoffpoint whenever you want to allow multuiple sessions to see the same display until you increment the cutoffpoint.

2 added 230 characters in body
source | link

BTW : The reason the algorithm uses LEFT JOIN instead of INNER JOIN is simple. INNER JOIN loses the sort order created by the ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag clause. LEFT JOIN preserves it.

BTW : The reason the algorithm uses LEFT JOIN instead of INNER JOIN is simple. INNER JOIN loses the sort order created by the ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag clause. LEFT JOIN preserves it.

1
source | link

I have a very intricate solution based on the rowcount

Here is the basic algorithm

Run this line once

SET @cop:=0;

Then run these lines every interval

SET @x1:=0;
SELECT COUNT(1) INTO @rowcount FROM villas;
SET @cop:=MOD(@cop,@rowcount)+1;
SELECT A.* FROM 
(SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
FROM villas A1 ORDER BY random_order) AA
ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
LEFT JOIN villas A USING (uid);

@cop is Cut Off Point

Here is some sample data I loaded using your question + 2 extra villas

mysql> use test
Database changed
mysql> DROP TABLE villas;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE villas
    -> (
    ->   UID INT,
    ->   name VARCHAR(20),
    ->   random_order INT
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO villas VALUES
    -> ( 4,'villaA',12),
    -> ( 8,'villaB', 5),
    -> ( 9,'villaC', 9),
    -> (11,'villaD', 2),
    -> (13,'villaE',30),
    -> (15,'villaF',50)
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM villas;
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|    4 | villaA |           12 |
|    8 | villaB |            5 |
|    9 | villaC |            9 |
|   11 | villaD |            2 |
|   13 | villaE |           30 |
|   15 | villaF |           50 |
+------+--------+--------------+
6 rows in set (0.00 sec)

After running SET @cop:=0;, every time you run all the other lines, the list rotates. Note that I ran those lines 7 times with 6 rows. Here is the output:

mysql> SET @cop:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @x1:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
SET @cop:=MOD(@cop,@rowcount)+1;
SELECT A.* FROM
Query OK, 1 row affected (0.00 sec)

(SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
mysql> SET @cop:=MOD(@cop,@rowcount)+1;
LEFT JOIN villas A USING (uid);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|   11 | villaD |            2 |
|    8 | villaB |            5 |
|    9 | villaC |            9 |
|    4 | villaA |           12 |
|   13 | villaE |           30 |
|   15 | villaF |           50 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql> SET @x1:=0;
SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
SELECT A.* FROM
Query OK, 1 row affected (0.00 sec)

mysql> SET @cop:=MOD(@cop,@rowcount)+1;
ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
Query OK, 0 rows affected (0.00 sec)

LEFT JOIN villas A USING (uid);
mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|    8 | villaB |            5 |
|    9 | villaC |            9 |
|    4 | villaA |           12 |
|   13 | villaE |           30 |
|   15 | villaF |           50 |
|   11 | villaD |            2 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql> SET @x1:=0;
SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 1 row affected (0.00 sec)

mysql> SET @cop:=MOD(@cop,@rowcount)+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|    9 | villaC |            9 |
|    4 | villaA |           12 |
|   13 | villaE |           30 |
|   15 | villaF |           50 |
|   11 | villaD |            2 |
|    8 | villaB |            5 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql> SET @x1:=0;
SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 1 row affected (0.00 sec)

mysql> SET @cop:=MOD(@cop,@rowcount)+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|    4 | villaA |           12 |
|   13 | villaE |           30 |
|   15 | villaF |           50 |
|   11 | villaD |            2 |
|    8 | villaB |            5 |
|    9 | villaC |            9 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql> SET @x1:=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 1 row affected (0.00 sec)

mysql> SET @cop:=MOD(@cop,@rowcount)+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|   13 | villaE |           30 |
|   15 | villaF |           50 |
|   11 | villaD |            2 |
|    8 | villaB |            5 |
|    9 | villaC |            9 |
|    4 | villaA |           12 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql> SET @x1:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 1 row affected (0.00 sec)

mysql> SET @cop:=MOD(@cop,@rowcount)+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|   15 | villaF |           50 |
|   11 | villaD |            2 |
|    8 | villaB |            5 |
|    9 | villaC |            9 |
|    4 | villaA |           12 |
|   13 | villaE |           30 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql> SET @x1:=0;
SET @cop:=MOD(@cop,@rowcount)+1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @rowcount FROM villas;
Query OK, 1 row affected (0.00 sec)

mysql> SET @cop:=MOD(@cop,@rowcount)+1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT A.* FROM
    -> (SELECT uid,random_order FROM (SELECT uid,random_order,@x1:=@x1+1 ordertag
    -> FROM villas A1 ORDER BY random_order) AA
    -> ORDER BY IF(ordertag >= @cop,0,1),IF(ordertag < @cop,0,1),ordertag) B
    -> LEFT JOIN villas A USING (uid);
+------+--------+--------------+
| UID  | name   | random_order |
+------+--------+--------------+
|   11 | villaD |            2 |
|    8 | villaB |            5 |
|    9 | villaC |            9 |
|    4 | villaA |           12 |
|   13 | villaE |           30 |
|   15 | villaF |           50 |
+------+--------+--------------+
6 rows in set (0.00 sec)

mysql>

All you have to do is set whichever row is the cut off point ONCE.

Give it a Try !!!