My SQL order by 2 fields with one field changing each time

Question

I have a table that looks like this:

There are 100 questions.

Table Structure for "Questions Table":

title: 
answerA:
answerB:
answerC:
answerD:
category:
difficulty:

Each question can have one 1 of 5 categories("1,2,3,4 or 5") and 1 out of 3 difficulty levels("easy, medium OR hard").

Currently I am sorting them by category and randomly such as this:

"SELECT * FROM Questions order by category, rand()";

So now the questions are sorted by category and randomized.

I would like that the results come ALSO with different difficulty levels for each category.

So something like this:

question 1 , category 1, difficulty easy
question 3 , category 1, difficulty medium
question 4 , category 1, difficulty hard
question 2 , category 1, difficulty easy


question 5 , category 2, difficulty easy
question 6 , category 2, difficulty medium
question 7 , category 2, difficulty hard
question 2 , category 2, difficulty easy


question 8 , category 3, difficulty easy
question 10, category 3, difficulty medium
question 9 , category 3, difficulty hard
question 0 , category 3, difficulty easy

and so on.

How would I replicate the above? It seems that I need 2 criteria in my sorting and the difficulty criteria needs to change each time a question is printed for a new category.

Most of the Google results I found have to do with categorizing by 2 fields but with the same order.

So you want to select 2 easy, 1 medium and 1 hard for each category? — Jakub Kania, Aug 25 '14 at 10:22
@JakubKania no just to make sure that the 1st question printed for each category is "easy", 2nd is "medium", 3rd is "hard", 4th is "easy", 5th is "medium", 6th is "hard" again and so on. — nicholaswmin, Aug 25 '14 at 10:23
And you are sure that you want to do that in a database instead of your app? It's doable but no solution will be nice. — Jakub Kania, Aug 25 '14 at 10:46
@JakubKania If it can be reliably done in a query I would prefer since I don't want to mess with the PHP thats already there. — nicholaswmin, Aug 25 '14 at 10:48

score 1 · Accepted Answer · edited May 23 '17 at 10:25

Okay. So to such an order we're gonna needed a grouped rank first:

(SELECT title, category, difficulty,
   @rank:=CASE 
            WHEN @category <> category OR @difficulty <> difficulty 
                 THEN 0 
            ELSE @rank+1 E
          ND AS rn,
   @difficulty = difficulty,
   @category   = category
FROM
  (SELECT @rank:= -1) s,
  (SELECT @category:= -1) c,
  (SELECT @difficulty:= -1) d,
  (SELECT *
   FROM questions
   ORDER BY category, difficulty, rand()
  ) t) as q

This creates us a tables where each question has it's rank in category. Now let's sort it:

SELECT *
FROM (...) as q
ORDER BY category, rn, CASE 
                          WHEN difficulty = 'easy' 
                              THEN 1 
                          WHEN difficulty = 'medium' 
                              THEN 2
                          ELSE 3
                       END;

My SQL order by 2 fields with one field changing each time

1 Answers1