CODEWITHSUNDEEP
mysqldate
deceze
deceze

Reputation: 522210

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

Upvotes: 1

Views: 4058

Answers (2)

ZA.
ZA.

Reputation: 10477

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

Upvotes: 0

Blixt
Blixt

Reputation: 50179

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

Upvotes: 3

Related Questions