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.
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)
Has anyone have any idea of the mysql query I should use?