title: Incorrect row numbering sequence
body: <p>I have two tables (questions and answers) with a row number for questions and another for answers. The row number for questions increments when the question changes. The row number for answers increments each answer and resets to 1 for each new question. Both row numbering increments as intended, but the sort order is wrong. Also, I don't want the query outputs the variable that checks if the current record's question matches the last records question.</p>
  
  <p>I believe the problem is the record sorting is performed last, when it should be the variables' assignment that happens last. I've tried to adapt the solution at <a href="https://stackoverflow.com/questions/19721651/mysql-get-row-position-with-order-by-incorrectly-numbered">MySQL get row position with ORDER BY incorrectly numbered</a> but I keep getting errors.  I don't know how to 'turn off' the @currQ being displayed.</p>
  
  <p>Table questions has (question_id,question,display_order)
  Table answrs has (answer_id, question_id_fk, answer, display_order)</p>
  
  <pre><code>SET @q_row=0,@a_row=0,@currQ='';
  SELECT
      @q_row:=CASE WHEN q.question=@currQ THEN @q_row ELSE @q_row+1 END AS
      'Question No.',
      q.question,
      @a_row:=CASE WHEN q.question=@currQ THEN @a_row+1 ELSE 1 END AS
      'Answer
      No.',
      a.answer, @currQ:=q.question
  
  FROM
      questions q
  
  INNER JOIN
      answers a ON q.question_id=a.question_id_fk
  
  ORDER BY
      q.question,a.answer ASC;
  </code></pre>
  
  <p>The dynamic numbering works, but the sequence is in the wrong order.. </p>
  
  <pre><code>Question No.   question            Answer No.    answer   @currQ:=q.question
  4              Favourite excercise 3             Archery  Favourite excercise
  4              Favourite excercise 1             Running  Favourite excercise
  4              Favourite excercise 2             Sailing  Favourite excercise
  2              Favourite food      3             French   Favourite food
  2              Favourite food      2             South... Favourite food
  2              Favourite food      1             Indian   Favourite food
  2              Favourite food      4             Vietnam..Favourite food
  1              Favourite band      2             Deep P.. Favourite band
  1              Favourite band      1             Jimi H.. Favourite band      
  1              Favourite band      3             Eagles   Favourite band      
  1              Favourite pet       1             Dog      Favourite pet      
  </code></pre>
  
  <p>This is how I'd like it to look (using different data)</p>
  
  <pre><code>QRow   Question       Q.ID  Q.disp_ord  ARow  Answer        A.ID  A.disp_ord
    1       Favourite Pet   19     6        1    Dog            17     4
    2       Favourite Band   8     9        1    The Who         3     1
    2       Favourite Band   8     9        2    Dire Straits   69     3
    2       Favourite Band   8     9        3    The Doors      103    15
    3       Best Food       26     15       1    Thai            76    1
    3       Best Food       26     15       2    Japanese       233    2
  </code></pre>
  
  <p>Ordering from Q.disp_ord, A.disp_ord. The results show that both disp_ord must ascend, but note they may not be sequential (as other questions and answers within the sequence may be filtered out).  </p>