Incorrect row numbering sequence

Question

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.

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 MySQL get row position with ORDER BY incorrectly numbered but I keep getting errors. I don't know how to 'turn off' the @currQ being displayed.

Table questions has (question_id,question,display_order) Table answrs has (answer_id, question_id_fk, answer, display_order)

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;

The dynamic numbering works, but the sequence is in the wrong order..

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

This is how I'd like it to look (using different data)

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

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).

You might need to nest the query you currently have. The containing query would then be used to only select the first four columns from the inner query. You can then also move the ordering to the outer query. Example: https://stackoverflow.com/questions/19964640/mysql-nested-select-query — Markus Deibel, Sep 09 '19 at 07:39
Thanks - but still doesn't sort properly (on questions.display_order ASC & answers.display_order ASC). — Hubs, Sep 09 '19 at 12:44
I've played a bit with SQLFiddle and over there the system seems to get the ordering right: http://www.sqlfiddle.com/#!9/c4d2a8/2 — Markus Deibel, Sep 09 '19 at 13:40
Thanks - SQL fiddle is a handy tool. But when the recordset is sorted by 'display_order' you can see that the row numbering is out of sequence. Have a look at my demo on http://www.sqlfiddle.com/#!9/bed44a/1 . It works on your demo because the sorting is on the natural key, but my application will require sorting on another column. — Hubs, Sep 10 '19 at 01:30
I've changed the demo with different content which I thought easier to interpret at http://www.sqlfiddle.com/#!9/d9b260/1 — Hubs, Sep 10 '19 at 01:35
Based on your fiddle I've come up with this http://www.sqlfiddle.com/#!9/a8408/8 I explicitely left the commented line in so you can directly see which part I changed and moved out of the inner `SELECT` — Markus Deibel, Sep 10 '19 at 05:11
Thanks for helping, but I need to sort on question.display_order, then answer.display_order. The reason is, the questionaire will follow an sequence that isn't the same as the sequence of data entry. In other words, I might want the question entered fifth to display as the first question, and so on. — Hubs, Sep 10 '19 at 07:00
Please update the question, then you can also use formatting and it becomes more readable ;) — Markus Deibel, Sep 10 '19 at 07:32
Now with the q.disp_ord and a.disp_ord shown in the edited question. — Hubs, Sep 10 '19 at 08:49
I think the following fiddle might work: http://www.sqlfiddle.com/#!9/4cd3b0/27 You can check the steps in between by higlighting the inner `SELECT` statements and "Run SQL" to see the `FullData` and `OrderedData` results — Markus Deibel, Sep 10 '19 at 09:26

score 0 · Accepted Answer · answered Sep 10 '19 at 13:52

I'm leaving the final result (copied from SQLFiddle) here for reference:

We basically have three steps.

Get all the data in the right order (FullData)
Add the QRow and ARow in the right order (OrderedData)
Filter out the columns we really want to see (most outer SELECT statement)

SET @q_row=0,@a_row=0,@currQ='';   

SELECT QRow, Question, ARow, Answer
FROM
(
  SELECT 
    @q_row:=CASE WHEN Question=@currQ THEN @q_row ELSE @q_row+1 END AS QRow, 
    Question, Q_ID, Q_DO, 
    @a_row:=CASE WHEN Question=@currQ THEN @a_row+1 ELSE 1 END AS ARow,
    Answer, A_ID, A_DO,
    @currQ:=Question
  FROM
  (
    SELECT    
       q.question AS Question, 
       q.question_id AS Q_ID, 
       q.display_order AS Q_DO,
       a.answer AS Answer,
       a.answer_id AS A_ID,
       a.display_order AS A_DO
       FROM questions q 
         INNER JOIN answers a ON q.question_id=a.question_id_fk 
         ORDER BY q.display_order, a.display_order
  ) FullData
) OrderedData

We got here with the following schema and data

CREATE TABLE IF NOT EXISTS `questions` (
  `question_id` int(6) unsigned NOT NULL,
  `question` varchar(20) NOT NULL,
  `display_order` int(6) unsigned NOT NULL,
  PRIMARY KEY (`question_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `questions` (`question_id`, `question`,`display_order`) VALUES
  (1, 'Favourite bands', 3), 
  (3, 'Favourite pet type', 1), 
  (4, 'Favourite sport', 2),
  (2, 'Favourite foods', 4); 

CREATE TABLE IF NOT EXISTS `answers` (
  `answer_id` int(6) unsigned NOT NULL,
  `question_id_fk` int(6) unsigned NOT NULL,
  `answer` varchar(20) NOT NULL,
  `display_order` int(6) unsigned NOT NULL,
  PRIMARY KEY (`answer_id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `answers` (`answer_id`, `question_id_fk`, `answer`,`display_order`) VALUES
  (15, 1, 'Bruce Springsteen',2), 
  (23, 1, 'Jimi Hendrix',3), 
  (32, 1, 'The Beatles',1), 
  (25, 2, 'Street Vietnamese',2), 
  (14, 2, 'Spicy Indian',1), 
  (62, 3, 'Dog',2), 
  (17, 3, 'Cat',1), 
  (83, 4, 'NBA',3),
  (119, 4, 'Australian Rules',2),
  (11, 4, 'Golf',4),
  (12, 4, 'Sailing',1)

Incorrect row numbering sequence

1 Answers1