MySQL order by question

Question

I got items rated by both ratings, and use an ORDER BY key to order them by positive, but with an example table:

//(item)-(pos)-(neg):
item 1 - 50 - 200
item 2 - 24 - 0
item 3 - 15 - 12

But item 1 is really less rating than the others, how would I fix this?

Answer 1

You can find the net score by subtracting neg from pos, then order by that net score. Calculate it, then give it an alias, we'll call it net:

mysql> SELECT item, pos, neg, (pos - neg) AS net
    -> FROM table
    -> ORDER BY net DESC;
+------+-----+-----+------+
| item | pos | neg | net  |
+------+-----+-----+------+
|    2 |  24 |   0 |   24 |
|    3 |  15 |  12 |    3 |
|    1 |  50 | 200 | -150 |
+------+-----+-----+------+
3 rows in set (0.00 sec)

Answer 2

You can do ORDER BY pos - neg DESC but it will be slow because it cannot use an index.

You can denormalize your database by explicitly storing the value of pos - neg in the table so that you can index it to improve performance. Usually denormalization is regarded as a bad thing and should be avoided but if it the improved performance is required then it is an acceptable approach.

Answer 3

Yes you can use math in order.

ORDER BY pos-neg DESC

BUT make sure pos and neg are not UNSIGNED! Because when they try to go negative you will end up with biggest result ever ;)