MySQL WHERE timestamp >= SUBDATE(MAX(timestamp), INTERVAL 5 DAY)

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().

Answer 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

Answer 2

SELECT *
FROM table
where `timestamp` >= (select SUBDATE(MAX(`timestamp`), INTERVAL 5 DAY) from table )
ORDER BY RAND()
LIMIT 10;