Rick Joe Rick Joe - 4 months ago 9
MySQL Question

show 8 posts from a top 200 - last month

I have this select to show posts order by visits:

SELECT id, date, fotos, title FROM posts order by visits desc limit 8


the problem is, I want to alternate the most visit posts that I have in the last month. Show only 8 results but each time it shows 8 from a top 200, for example.

any ideas?

Answer
SELECT * FROM (SELECT id, date, fotos, title FROM posts 
WHERE YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
ORDER BY visits DESC LIMIT 0,200) tmp ORDER BY RAND() LIMIT 0,8