Real time ranking in mysql

Hello, I wanted to ask you guys how would you do this…
I have a site, that people can comment a post, each post will be stored in a mysql database and stored with a unix timestamp… (I use php+mysql).

Well, the idea is to create a real time ranking, for example, how could I know which are the most active posts in the last 24 hours? (the query should get the comments in the last 24 hours + order by the number of comments per post.
Database estructure:
POST_ID, COMMENT_ID, UNIX_TIMESTAMP, COMMENT
24, 1, 2132135435, I like chocolate
24, 2, 2132135648, me too
24, 3, 2132135648, who doesn’t like chocolate??
25, 4, 32153553153, does anyone gone to ny??
25, 5, 3213213553, I have gone to NY!!
It should first extract all the comments in the last 24 hours, and after order by the number of comments per POST_ID and ORDER them in Descendent.

POST_ID: 24 (5 comments)
POST_ID:59 (4 comments)
POST_ID:13(2 comments)
POST_ID:10(1 comment)

Has anyone have any idea of the mysql query I should use?

Thank’s friends!
visionariAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis AnifantakisProgramming InstructorCommented:
select post_id, count(post_id) as totalposts
from mytable
group by post_id
order by count(post_id) desc

that will give you

post_id, totalposts
------------------
24,5
59,4
13,2
10,1

Just as you asked :)
0
Ioannis AnifantakisProgramming InstructorCommented:
Also, I forgot you want the today's results
So here it is taking care of the date as well

select post_id, count(post_id) as totalposts
from mytable
where date(unix_timestamp)=current_date
group by post_id
order by count(post_id) desc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visionariAuthor Commented:
Thank's! works perfect!
0
Ioannis AnifantakisProgramming InstructorCommented:
no problem mate!
Anytime
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.