I have a table with rows of quotes (ID, QUOTE) and I have a query to get a random quote (ORDER BY RAND() LIMIT 0,1). I know it's slow but I dont have too many rows. So now I would like to get a quote of the day. It should be a random quote but unchangeable during a day. I don't want to add new columns, and I don't want to select the quote of the day. I just want a query that will get a random quote but in a way that the quote will be the same for whole day and the next day it will return other random quote. How to do this SQL Palladins?
-
I suggest you to have a scheduled task that would get a random quote and save it to some text file. Have this task execute once a day. And read contents of this file, rather than making a query to database everytime you want to load the quote of the day.yasar– yasar2012-06-14 21:18:41 +00:00Commented Jun 14, 2012 at 21:18
Add a comment
|
2 Answers
Use a seed for the random number generator based on the current date:
ORDER BY RAND(20120714) LIMIT 1
I'm also curious about what you meant by "other" here:
and the next day it will return other random quote.
If you want to select a different random quote each day then you shouldn't use a simple ORDER BY RAND(seed). This could return the same quote three days in a row.
If you want to avoid this happening you can instead store which quote is selected for each day and choose a random quote from those that haven't been used in the last n days. Of course, if you are storing when a quote was last used then there is also a natural solution to your original problem:
- Look for a quote that was last used today.
- If there is one, that's the quote of the day.
- If not, then select a new quote of the day from rows that haven't been used in the last n days, and update the row to show that it is the quote of the day for today.
I don't want to add new columns
OK, how about a slightly different approach:
ORDER BY RAND(201207) LIMIT 14, 1
-- yyyymm dd
Now you don't need an extra column and you won't get repeats, except perhaps when the month changes. This solution assumes that you have at least 32 quotes to choose from.
4 Comments
Tom Smykowski
I have like 1 thousand rows so it's not a big risk that i have 2 times the same quote. And even if it will occur - it's not a biggie.
Mark Byers
@tomaszs: The sort order will be the same for every day in a month, and each day it takes the next one from the list (the limit offset changes). When it reaches the next month, the list is reshuffled.
Tom Smykowski
+Marks Impressive solution. Thanks !
Vignesh PT
This doesn't work with SQLite. Do we have a solution for this problem in sqlite as well?
Solving this problem could involve writing an application that would select your IDs max and min once a day; calculate a random number from that range; and then write that number to a file.
The application responsible for printing the quote would select the quote based on the ID that was stored in the file.
Enough languages like Python, Perl, PHP, and others have good DB interfaces these days. Python also has a random library.
