still thinking out loud here.
if we were to calculate datediff(todays date, fixed start date) and divide by 7, then we can calculate number of weeks since a fixed point in time.
taking a mod(20) of this value would always provide a number between 1 and 20, so this value can be used to select the 'active' row.
Using this method, we would always be in sequence.
does this seem plausible ?
***
If so, here is my rough and ready SQL...
SELECT * FROM toptenlist
WHERE topTenListID = (mod(round((datediff(curdate(), '2008-10-10')/7),0),3)+1)
2008-10-10 is just an arbitrary date
+1 is required at the end in case the modulus returns 0
(in the example above, i used a modulus of 3 (in red), but this number should be the same as the number of rows in the table.
Feel free to point out the errors in my logic !! :-)
It kept my brain going anyway.
Hope this helps
