Select query ordered by two subselects that depend on the outer query

Question

I have the following SQL schema (simplified) with example data.

Table vote
+--+-------+------+
|id|user_id|points|
+--+-------+------+
| 1|      1|     3|
| 2|      1|     1|
| 3|      1|     0|
| 4|      2|     2|
| 5|      2|     2|
| 6|      2|     0|
| 7|      3|     2|
| 8|      3|     1|
| 9|      3|     2|
+--+-------+------+

What I want to achieve is to select the user_id ordered by summed points, ordered by most number of 3 points and ordered by most number of 2 points.

So something like this:

SELECT v.user_id, SUM(v.points) FROM vote v ORDER BY SUM(v.points) DESC, (Number of 3 points) DESC, (Number of 2 points) DESC GROUP BY v.user_id

I'm struggling on setting up the two subqueries because I don't know how to keep track of the current user_id.

So in the case of above data I want the following result:

+-------+------+
|user_id|points|
+-------+------+
|      3|     5|
|      1|     4|
|      2|     4|
+-------+------+

User 3 is first, because he has a total of 5 points, user 1 is second place, because he had 3 points one time (one more than user 2).

Is this possible to achieve with SQL?

Answer 1

I believe what you want is something like this... you shouldn't need to do subqueries, rather just check the points, set a variable to it and order by that variable... I would need to test with more sample data but it provides the desired result

SELECT 
  v.user_id, 
  SUM(v.points) as addedNum,
  if(
      v.points = 3, @a := 2, if(v.points = 2, @a := 1, @a := 0)
  ) as num_p_three_two
FROM vote v 
JOIN (select @a := 0, @b := 0) as temp
GROUP BY v.user_id
ORDER BY 
addedNum DESC,
num_p_three_two DESC;

SQL FIDDLE to play with

I also changed the order of your query because group by goes before order by :)