
Question:
What's the best way to express this in one SQL query?
"Select a few random items that fall within x days of the newest item in the table."
I tried the following:
SELECT * FROM table HAVING `timestamp` >= SUBDATE(MAX(`timestamp`), INTERVAL 5 DAY) ORDER BY RAND() LIMIT 10
But this only gives me a single result, not 10. WHERE
instead of HAVING
doesn't cut it because of the use of MAX()
.
Solution:1
You probably want your MAX
statement in a sub-query:
SELECT * FROM table WHERE `timestamp` >= SUBDATE((SELECT MAX(`timestamp`) FROM table), INTERVAL 5 DAY) ORDER BY RAND() LIMIT 10
Solution:2
SELECT * FROM table where `timestamp` >= (select SUBDATE(MAX(`timestamp`), INTERVAL 5 DAY) from table ) ORDER BY RAND() LIMIT 10;
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon