SMF Support > SMF 2.0.x Support

Invalid SQL Query Sort?

(1/1)

Ice_Drake:
Hi, I am trying to get a query for the member with the highest total karma...(good karma - bad karma)... Problem is that I am not getting the right results.

On the top of the list is the member with the less negative total karma followed by the member with the most negative total karma followed by the member with the most positive total karma. After that, it is correct. I don't understand why negative total karma shows up in the result when I add the where clause requiring only positive result.

Here is the query I am making:


--- Code: --- $sql = $smcFunc['db_query']('','
SELECT mem.id_member, mem.real_name, mem.avatar, mem.karma_good, mem.karma_bad, mg.online_color
FROM {db_prefix}members AS mem
LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = IF(mem.id_group = 0, mem.id_post_group, mem.id_group))
WHERE SIGN(mem.karma_good - mem.karma_bad) >= 0
ORDER BY (mem.karma_good - mem.karma_bad) DESC
LIMIT {int:limit}',
array(
'limit' => $limit,
)
);

--- End code ---

Need a little pointer?

Arantor:
So, you want the total karma for each user, excluding users whose karma is below 0, ordered by highest first. Try this:


--- Code: --- $sql = $smcFunc['db_query']('','
SELECT mem.id_member, mem.real_name, mem.avatar, mem.karma_good, mem.karma_bad, (karma_good - karma_bad) AS total_karma, mg.online_color
FROM {db_prefix}members AS mem
LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = IF(mem.id_group = 0, mem.id_post_group, mem.id_group))
WHERE mem.karma_good >= mem.karma_bad
ORDER BY total_karma DESC
LIMIT {int:limit}',
array(
'limit' => $limit,
)
);
--- End code ---

Ice_Drake:
Oh, wow...It doesn't occur to me with that comparison. Thank you so much.  ;)

It is strange though; I guess there is something wrong with my WHERE clause causing negative total karma to show up.

Arantor:
I find it's always best to boil it down to the simplest possible expression - if nothing else it'll be maintainable later, and frequently also faster.

Navigation

[0] Message Index