tags:

views:

319

answers:

2

I've got the following query to determine how many votes a story has received:

SELECT s_id, s_title, s_time, (s_time-now()) AS s_timediff, 

(
 (SELECT COUNT(*) FROM s_ups WHERE stories.q_id=s_ups.s_id) -
 (SELECT COUNT(*) FROM s_downs WHERE stories.s_id=s_downs.s_id)
) AS votes

FROM stories

I'd like to apply the following mathematical function to it for upcoming stories (I think it's what reddit uses) - http://redflavor.com/reddit.cf.algorithm.png

I can perform the function on the application side (which I'm doing now), but I can't sort it by the ranking which the function provides.

Any advise?

+1  A: 

y and z are the tricky ones. You want a specific return based on x's value. That sounds like a good reason to make a function.

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

You should make 1 function for y and one for z. pass in x, and expect a number back out.

DELIMINATOR //

CREATE FUNCTION y_element(x INT) 
  RETURNS INT

BEGIN
    DECLARE y INT;

IF x > 0 SET y =  1;
ELSEIF x = 0 SET y =  0;
ELSEIF x < 0 SET y = -1;
END IF;

RETURN y;

END //;

DELIMINATOR;

There is y. I did it by hand without checking so you may have to fix a few typo's. Do z the same way, and then you have all of the values for your final function.

J.J.
I forgot to give you this link as well http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.htmlThat you you don't have to reinvent the wheel for abs, and so on.p.s. I <3 Set Theory.
J.J.
=) Thanks, I'm gonna give this a try as well.
A: 

Try this:

    SELECT s_id, s_title, log10(Z) + (Y * s_timediff)/45000 AS redditfunction 
    FROM (
    SELECT stories.s_id, stories.s_title, stories.s_time, 
    stories.s_time - now() AS s_timediff, 
    count(s_ups.s_id) - count(s_downs.s_id) as X, 
    if(X>0,1,if(x<0,-1,0)) as Y, 
    if(abs(x)>=1,abs(x),1) as Z
    FROM stories 
    LEFT JOIN s_ups ON stories.q_id=s_ups.s_id
    LEFT JOIN s_downs ON stories.s_id=s_downs.s_id
    GROUP BY stories.s_id
    ) as derived_table1

You might need to check this statement if it works with your datasets.

Jonathan
Thanks for this, I tried it but it said "Every derived table must have its own alias"...
sorry :Sfixed now
Jonathan