problem in mysql query with join and limit the time

Question

i have this

SELECT COUNT(1) cnt, a.auther_id
                            FROM `posts` a 
                                LEFT JOIN users u ON a.auther_id = u.id
                                    GROUP BY a.auther_id
                                        ORDER BY cnt DESC
                                            LIMIT 20

its work fine bu now i want select from posts which added from 1 day tried to use

WHERE from_unixtime(post_time) >= SUBDATE(NOW(),1) 

but its didnot worked any one have idea

Answer 1

My guess is that you added the WHERE clause in the wrong place. It should come after the JOIN but before the GROUP BY, like this:

SELECT COUNT(1) cnt, a.auther_id
FROM `posts` a 
LEFT JOIN users u ON a.auther_id = u.id
WHERE from_unixtime(post_time) >= SUBDATE(NOW(),1) 
GROUP BY a.auther_id
ORDER BY cnt DESC
LIMIT 20