1

I have a table of villas ordered by the UID as follow :

UID, name, random_order:

  • 4, villaA, 12
  • 8, villaB, 5
  • 9, villaC, 9
  • 11, villaD, 2

I want to show my villas according to the random_order column (the random number is created at the insertion of the villa) so I sort by "random_order" and I get the result:

  • 11, villaD, 2
  • 8, villaB, 5
  • 9, villaC, 9
  • 4, villaA, 12

Until now it’s easy. The more difficult thing is that I want to make a turnover on the villas. Every hour the first villa becomes the last one.

After 1 hour:

  • 8, villaB, 5
  • 9, villaC, 9
  • 4, villaA, 12
  • 11, villaD, 2

After 2 hours:

  • 9, villaC, 9
  • 4, villaA, 12
  • 11, villaD, 2
  • 8, villaB, 5

and so on.

I don’t know how to do that. My example has 4 villas but of course I need to manage a lot more so I need something well optimized (I don’t want to update the column “random_order” every hour). Also note that a villa can be added at any time with a random value as “random_order”.

I was given the following solution (count is a variable incremented by 1 every hours and max_val is the maximum of the column “random_order”): SELECT *, (random_order - )%MAX_VAL AS villa_order FROM villa_table ORDER BY villa_order ASC But this is working only if my column “random_order” has unique values and different by 1 only and starting from 0… It’s far from my case.

Anyone has an idea?

I tried to make a select with limit from n to end union select limit from 0 to n but it doesn't accept a double limit in a single request. I could use a temporary table to put results in it but then wouldn't it be too slow?

Tks

|improve this question
  • Not sure how to do that... – viv Jan 2 '12 at 2:02
  • Isn't there a big V or check sign next to each of the answers? – Jannes Jan 2 '12 at 4:08
2

Not tested, but the idea is using same query twice with different LIMITs (depending on %HOURS passed) in a UNION.

(
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT %HOURS, 999999999999
) UNION ALL (
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT 0, %HOURS
)

You'll need to fill in %HOURS in your script language or stored procedure. Also once %HOURS is larger than the COUNT(*) of villa_table you'll need to restart it from 0.

Note how the parentheses are necessary.

Also note that the ORDER BY fields must uniquely identify rows (i.e. append the PRIMARY KEY!) to prevent possible ambiguous sorting.

|improve this answer
  • that's working! I didn't know how to write it properly. Perfect. I guess it is the most efficient solution (as long as there is an index on "villa_order") right? My solution that works with a modulo is not as good as this one and the modulo is adding some work that should make it slower than this one right? – viv Nov 29 '11 at 2:05
  • Yes this should use whatever indexes it can. The only inefficiency is that it runs the query twice I guess. The query in your question shouldn't be too bad either, efficiency wise. But as you said, it needs sequential and unique villa_order. If you ever need to add a WHERE clause, make sure you put it in twice in both queries. Don't try to put one big WHERE around the whole UNION. – Jannes Nov 29 '11 at 8:33
  • Small addition: if villa_order is not unique, theoretically the ORDER BY puts equal villa_orders in a random order. That could cause weird results up to even duplicate and missing records using the above query. So what you want to do is add more fields to the ORDER BY, preferably your PRIMARY KEY / ID field. ORDER BY villa_order ASC, ID ASC and of course do that in both subqueries. I also edited my answer to reflect this. – Jannes Nov 29 '11 at 8:40
1

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

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.

Give it a Try !!!

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.