Topic: Ranking Algorithm Into Query

[katlis]

So I have this reddit-esque ranking function...

Code: [Select]

function rank($ts,$z) {

$rating = log10($z) + (-1*$ts)/45000;
return $rating;

}

$z = number of votes
$ts = number of seconds between January 19th and the timestamp the entry was created... so I guess something like TIMESTAMPDIFF(SECOND,'2010-01-19 01:00:00',`post.published`)

I'm pretty lost on how to convert this into a query... I'd greatly appreciate any help anyone can provide on this.
Thanks! I'm using 5.1.43.

[katlis]

I think I'm getting there....

Code: [Select]

SELECT LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) FROM post ORDER BY `id` ASC;

Atleast I'm getting some sort of data back. Any one have input on how I can clean this up... and at the same time, return a list of the `id`'s ordered by the rating this formula is returning?
Thank you.

EDIT: A little more background on this... I have a list of articles that have votes in my "post" table. The important columns are `published` (the timestamp of publishing), `votes`, and `id`. I want to use the formula to sort through these entries and return the list of IDs ordered by rating of this "algorithm" (in desc).

[fenway]

If you alias that expression, you should be able to use it in the ORDER BY.

[katlis]

If you alias that expression, you should be able to use it in the ORDER BY.

Any chance you can show me an example of how that's done? I haven't done that before. Did a quick search, but can't seem to figure this one out.

[fenway]

Code: [Select]

SELECT LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) AS ranking FROM post ORDER BY ranking DESC;