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 !!!