Need help in constructing SQL SELECT statement to get "top 10 x"

mysql, sql

Solution

I would try something like this:

SELECT
  user_id,
  SUM( IF(`type`='add',+1,-1) * `value`) AS user_points
FROM Points
GROUP BY user_id
ORDER BY SUM( IF(`type`='add',+1,-1) * `value`) DESC
LIMIT 10

Seems to work: http://sqlfiddle.com/#!2/dd207/1

Alternative, maybe faster, maybe not, the trick is moving out the `SUM(IF())` part from the inner query:

http://sqlfiddle.com/#!2/dd207/9

SELECT
  user_id,
  SUM( IF(`type`='add',+1,-1) * `valuesum`)
FROM (
      SELECT
        user_id,
        `type`,
        SUM(`value`) AS `valuesum`
      FROM     Points
      GROUP BY user_id, `type`
     ) AS p
GROUP BY p.user_id
ORDER BY SUM( IF(`type`='add',+1,-1) * `valuesum`) DESC
LIMIT 10

Probably both solution have to do a full table scan to summarize everything, so this situation might be a good candidate for some wise denormalization (collecting sums per users in another table, for example).

Problem

I have this Points table: ``` =========================================== FIELD : TYPE =========================================== id int(11) user_id int(11) value int(11) type enum('add','subtract') timestamp int(11) =========================================== ``` Basically this table stores records of user points. Now my question is: How do I get the top 10 users with the highest amount of total points? Note: I have been told to change my schema -- drop the 'type' field and put positive and negative values for the 'value' field. I just want to know if I could achieve the desired query using the current schema. Thanks in advance

Original source