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

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

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

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

Labels