Determining popular tags using mysql?

I have two tables:

posts{id,post,tag_id,date开发者_高级运维}
tags{id,tag,date}

I want to retrieve the most popular tags used from the post table, used in the last 12 hours.

¿How can I achieve this?

Thanks.

I think this should work:

SELECT tags.tag, count(*) AS count
    FROM posts INNER JOIN tags
    ON (posts.tag_id = tags.id)
    WHERE posts.date >= SUBTIME(NOW(), '12:00:00')
    GROUP BY tags.id
    ORDER BY count(*) DESC

I tested with 3 posts with 'SQL' as tag created within 12h and 2 posts with 'PHP' as tag but only one of them created within 12h. The query above then returns:

tag count
SQL 3
PHP 1

To get the most popular tags in descending order from the last 12 hours you could do this:

SELECT tag  
FROM tags 
WHERE id IN (
    SELECT tag_id FROM posts WHERE date >= SUBTIME(NOW(), '12:00:00')
)
ORDER BY COUNT(id) DESC