How do I alias this expression to sort my results?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
katlis
Forum Newbie
Posts: 10
Joined: Mon Nov 05, 2007 12:42 pm

How do I alias this expression to sort my results?

Post 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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do I alias this expression to sort my results?

Post 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]
katlis
Forum Newbie
Posts: 10
Joined: Mon Nov 05, 2007 12:42 pm

Re: How do I alias this expression to sort my results?

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do I alias this expression to sort my results?

Post 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
katlis
Forum Newbie
Posts: 10
Joined: Mon Nov 05, 2007 12:42 pm

Re: How do I alias this expression to sort my results?

Post 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'?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do I alias this expression to sort my results?

Post 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.
katlis
Forum Newbie
Posts: 10
Joined: Mon Nov 05, 2007 12:42 pm

Re: How do I alias this expression to sort my results?

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do I alias this expression to sort my results?

Post 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
Post Reply