mysql order by multiple occurence

I didn't really know what to title this question, so that was my best guess.

Here is my problem. I have a table that looks like this:

Structure
__________________
problemID | actualDifficulty | personID | userDifficulty

Rows
__________________
39414 | 5 | 100001| 1
39414 | 5 | 100000| 1
39411 | 5 | 100000| 3
39411 | 5 | 100001| 3
39416 | 4 | 100001| 4
39413 | 3 | 100001| 3
39415 | 1 | 100001| 1
39412 | 1 | 100001| 1
39412 | 1 | 100000| 1

So there are a bunch of different "problems" that have a certain difficulty on a scale of 1-easy to 5-hard. The table above is each user doing the problem and saying how difficult it was for them to solve the problem.

What I am wanting is to get a "top 50" list where it will rank everyone based on a few things.

someone that solves a problem with a difficulty of 5 is always ranked higher than someone who hasn't. even if the other person has solved 100 problems with a difficulty of 4
If two people have solved the same number of problems with a difficulty of 5, it then goes to the userDifficulty column so that if one user said it was a 1 difficulty for them they would be ranked higher than someone who ranked the same problem as a 3
After this, it would go to whoever has solved the most number of problems with a difficulty of 4 ... and then solved it easiest ... and then who solved the most 3's ... and solved them easiest

Get the idea? Kinda?

Here is what I have so far

SELECT COUNT( * ) , personID
FROM table
WHERE actualDifficulty =5
GROUP BY personID
ORDER BY userDifficulty ASC
LIMIT 0 , 50

Any help?

Solution

This will rank your users by the hardest problem they've solved. I present it not because it answers your question, but because it's simple.

  SELECT COUNT(*) num, 
         MAX(actualDifficulty) actuaDifficulty, 
         MAX(userDifficulty) userDifficulty, 
         personID
    FROM probs
GROUP BY personID
ORDER BY 2 DESC, 3 DESC, 1 DESC
   LIMIT 0 , 50

(Notice I'm using column ordinals in the ORDER BY clause to keep things simple.)

Here's the actual answer to your question. It depends on the MySQL tidbit that the expression actualDifficulty = 5 has a value of 1 if that's true and 0 otherwise. So there's a nice little SUM(actualDifficulty = 5) hack possible. (http://sqlfiddle.com/#!9/57612b/1/0)

SELECT COUNT(*) num, 
       SUM(actualDifficulty = 5) actual5,  /* count of actualDiff = 5 */
       SUM(userDifficulty = 5) user5,      /* count of userDiff = 5 */
       SUM(actualDifficulty = 4) actual4,  /* count of actualDiff = 4 */
       SUM(userDifficulty = 4) user4,      /* count of userDiff = 4 */
       SUM(actualDifficulty = 3) actual3,  /* etc.... */
       SUM(userDifficulty = 3) user3,
       SUM(actualDifficulty = 2) actual2,
       SUM(userDifficulty = 2) user2,
       SUM(actualDifficulty = 1) actual1,
       SUM(userDifficulty = 1) user1,
       MAX(actualDifficulty) MaxActuaDifficulty, 
       MAX(userDifficulty) MaxUserDifficulty, 
       personID
  FROM probs
  GROUP BY personID
  ORDER BY 2 DESC, 3 DESC, 4 DESC, 5 DESC, 6 DESC, 
           7 DESC, 8 DESC, 9 DESC, 10 DESC, 11 DESC
LIMIT 0 , 50

Your specification says that you want to rank people by the number of problems they've solved with difficulty 5. SUM(actualDifficulty = 5) computes that. It's the second column in the result set, so ORDER BY 2 DESC ranks the users on that.

You went on to say that if two users had solved the same number of difficulty-5 problems, you should rank the one with higher user difficult (I think that's what you meant). So the next clause orders that way.

And so on for difficulty 4, 3, 2, 1.