Page 1 of 1
How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 3:49 pm
by katlis
So I've turned this ranking formula into a query:
Code: Select all
// $ts = time in seconds between an entry and a given date (lets say January 19th)
// $z = number of "votes" an entry has
function rank($ts,$z) {
$rating = log10($z) + (-1*$ts)/45000;
return $rating;
}
Code: Select all
SELECT LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) FROM post ORDER BY `id` DESC;
But I can't figure out how to return the results sorted by the rank that's given. I basically need to display a list of ids ordered by the calculated ranking (descending).
I greatly appreciate the help!
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 3:52 pm
by Eran
wrap the expression in quotes and add an alias -
[sql]SELECT ( LOG10(`votes`)+(-1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) ) AS `rank` FROM post ORDER BY `rank` DESC;[/sql]
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 3:58 pm
by katlis
pytrin wrote:wrap the expression in quotes and add an alias -
Thank you very very much.
So now I'm able to return the results along with the id using...
[sql]SELECT ( LOG10(`votes`)+(1*(TIMESTAMPDIFF(SECOND,'2010-01-19 11:00:00',`published`))/45000) ) AS `rank`, `id` FROM polls ORDER BY `rank` DESC;[/sql]
Did my query translate well from the php version? Is this a valid way of going about ranking entries like this? Or is there a better way using separate tables?
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 4:08 pm
by Eran
Yes, it's a perfectly valid way to rank entries. The only "problem" with it is that you can't use an index for sorting, which will have some performance implications when the table gets larger. At that point you could store the results of ranking in a dedicated field in the table (named 'rank' or something similar), index it and sort by it
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 4:18 pm
by katlis
pytrin wrote:Yes, it's a perfectly valid way to rank entries. The only "problem" with it is that you can't use an index for sorting, which will have some performance implications when the table gets larger. At that point you could store the results of ranking in a dedicated field in the table (named 'rank' or something similar), index it and sort by it
Ok cool, so indexing and storing the 'rank' in each row would be beneficial, even if the rank is changing each time the entry receives a new 'vote'?
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 4:21 pm
by Eran
That's the problem with storing the rank calculation - it stops being really 'real-time'. You need to run scheduled updates (cron jobs or task scheduler) in whatever intervals is acceptable to your application. But I'll repeat, only start going this way when you feel a need performance-wise. Your previous solution will work very well for a small tables.
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 4:25 pm
by katlis
pytrin wrote:That's the problem with storing the rank calculation - it stops being really 'real-time'. You need to run scheduled updates (cron jobs or task scheduler) in whatever intervals is acceptable to your application. But I'll repeat, only start going this way when you feel a need performance-wise. Your previous solution will work very well for a small tables.
Awesome, thanks. Last question. I haven't yet worked with trying to fit a number like -525.156593861166 into a column (an example of a ranking returned). What field type should I be using for numbers like this?
Re: How do I alias this expression to sort my results?
Posted: Wed Feb 24, 2010 4:40 pm
by Eran
You should use the FLOAT type if small rounding errors are not a concern for you - which I would think would be the case here. Use DECIMAL if you need exact representation of the number (mostly used for fiscal numbers).
http://dev.mysql.com/doc/refman/5.0/en/ ... types.html