How can make sort by votes "by date", "by year"..?

Question

What I am trying to implement is similar to what we have on SO. I want to rank posts by upvotes in last day, last month etc. My schema makes up two tables,

post(id, post, posted_on..)
vote(post_id, vote_value, date)

I hope the schema is pretty self explanatory. The problem being, if I sort "by day" by making a inner join on posts and vote and having a where clause('votes.date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY'), it does work as intended but fails to show the other posts. I mean the posts which haven't had vote in last day are completely ignored. What I want is that those posts be given low priority but do show up in the query.

While, I may think of using union operation but i was looking for another approach.

Update: Lets say, there are two posts, 1,2.

and votes table is like,

post_id       vote_value      date
1              1              2012-12-19
2              1              2012-12-10

If I query, as per my approach, then only the post - "1" will show up since I have put a date constraint but I want both to show up. Here is my query:

SELECT `id`, SUM(`votes`.`votes`) AS likes_t, `post`.* FROM `posts` JOIN `votes` ON (`id` = `votes`.`post_id`) WHERE `votes`.`date` >= DATE_SUB(CURDATE(), INTERVAL 2 DAY) 

Answer 1

If you want to show all posts, but only count the recent votes, this should do it:

SELECT `id`,
       SUM(IF(`votes`.`date` >= DATE_SUB(CURDATE(), INTERVAL 2 DAY, `votes`.`votes`, 0)) AS likes_t, 
       `post`.*
       FROM `posts` JOIN `votes` ON (`id` = `votes`.`post_id`)