Weekly ORDER BY on MySQL

Question

I want to select an entry from my database randomly, but not for every call, but depending on some other external factor like week of the year or day of the year. How can i achieve this?

This will tell me the current week:

SELECT DATE_FORMAT(CURDATE(),'%u'); 

So, to get a record based on this value, I select the record number:

 SELECT (DATE_FORMAT(CURDATE(),'%u')+0) MOD (SELECT COUNT(*) FROM my_view ORDER BY id); 

So my attempt:

 SELECT * FROM my_view LIMIT (SELECT (SELECT DATE_FORMAT(CURDATE(),'%u')+0) MOD (SELECT COUNT(*) FROM my_view ORDER BY id)) ,1; 

And all I get is:

 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT (SELECT DATE_FORMAT( CURDATE(),'%u')+0) MOD (SELECT COUNT(*) FROM ' at line 1 

Maybe this is not the best approach? Is it impossible? Thanks.

UPDATE: I want to insert this into VIEW, so I can only use SELECT statements. Anyways, I already solved this, please see this comment: Weekly ORDER BY on MySQL

Answer 1

The reason for the error you see is that the LIMIT can only accept literals as arguments, it does not accept variables.

If you want to select a different (random) number of rows, you can create a function that takes the desired number of rows and selects the rows in the temporary table in cycles. After that, you can simply select everything from this table.

If you want to select a record based on some data, then you should play with ORDER BY and select the N first lines.

Answer 2

According to the documentation :

LIMIT takes one or two numeric arguments, which must be non-negative integer constants, with these exceptions:

• Within prepared statements, the LIMIT parameters can be specified with ? placeholders.

• Within stored programs, LIMIT parameters can be set using parameters using integer parameters or local variables.

So a possible solution might be something like this:

 SET @offset = WEEKOFYEAR(CURDATE()) MOD (SELECT COUNT(*) FROM my_view); PREPARE STMT FROM 'SELECT * FROM my_view LIMIT ?, 1'; EXECUTE STMT USING @offset;