Implementing the Hacker News ranking algorithm in SQL

Question

Here's how Paul Graham describes the ranking algorithm for Hacker News:

News.YC's is just

(p - 1) / (t + 2)^1.5

where p = points and t = age in hours

I'd like to do that in pure mySQL given the following tables:

• Table Posts with fields postID (index) and postTime (timestamp).
• Table Votes with fields voteID (index), postID, and vote (integer, 0 or 1).

The idea of the vote field is that votes can be rescinded. For the purposes of the ranking, vote=0 is equivalent to no vote at all. (All votes are upvotes, no such thing as downvotes.)

The question is how to construct a query that returns the top N postIDs, sorted by Paul Graham's formula. There are approximately 100k posts altogether so if you think caching of the scores or anything will be needed, I'd love to hear advice about that.

(Obviously this is not rocket science and I can certainly figure it out but I figured someone who eats SQL for breakfast, lunch, and dinner could just rattle it off. And it seems valuable to have available on StackOverflow.)

---

Related questions:

• Hacker News style ordering algorithm in Linq-To-SQL
• How To Sort Like Hacker News
• https://meta.stackexchange.com/questions/11602/what-formula-should-be-used-to-determine-hot

Answer 1

$sql=mysql_query("SELECT * FROM news 
                         ORDER BY ((noOfLike-1)/POW(((UNIX_TIMESTAMP(NOW()) - 
                         UNIX_TIMESTAMP(created_at))/3600)+2,1.5)) DESC 
                 LIMIT 20");

This code works for me to make a home page like HN.

news: is the table name.

noOfLike: Total # of user like this news.

created_at: TimeStamp that when that news posted