MySQL Records Turnover: How to do it? 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
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.