If I use varaibles in the order by clause, is there any guarantee the result will be sorted? I have a table like this:

// Table1
+----+--------------------+------+------------------+---------+-----------+
| Id |  QuestionOrAnswer  | Type | AcceptedAnswerId | related | timestamp |
+----+--------------------+------+------------------+---------+-----------+
| 1  | question1          | 0    | 6                | 1       | 1         |
| 2  | answer1            | 1    | NULL             | 1       | 2         |
| 3  | question2          | 0    | 5                | 3       | 3         |
| 4  | answer2            | 1    | NULL             | 1       | 4         |
| 5  | answer3            | 1    | NULL             | 3       | 5         |
| 6  | answer4            | 1    | NULL             | 1       | 6         |
| 7  | answer5            | 1    | NULL             | 3       | 7         |
+----+--------------------+------+------------------+---------+-----------+


Now I want this result for question1: (please focus on the order)

+----+--------------------+------+------------------+---------+-----------+
| Id |  QuestionOrAnswer  | Type | AcceptedAnswerId | related | timestamp |
+----+--------------------+------+------------------+---------+-----------+
| 1  | question1          | 0    | 6                | 1       | 1         |
| 6  | answer4            | 1    | NULL             | 1       | 4         |
| 2  | answer1            | 1    | NULL             | 1       | 2         |
| 4  | answer2            | 1    | NULL             | 1       | 4         |
+----+--------------------+------+------------------+---------+-----------+
//                                                    ^ links question and related answers
//                          ^ 0 means question and 1 means answer




How can I do that?
OK,lets try to write a query that:


doesn't use variables in any way:
is correct and produces the wanted output always:

SELECT q.*, 0 as aa_id
FROM table1 AS q
WHERE q.type = 0

UNION ALL

SELECT ans.*, q.acceptedanswerid as aa_id 
FROM table1 AS q
  JOIN table1 AS ans   
    ON ans.related = q.id  
WHERE q.type = 0
  AND ans.type = 1

ORDER BY 
    type,
    CASE WHEN id = aa_id THEN 0 ELSE 1 END,
    timestamp ;



or simplified with a LEFT JOIN:

    SELECT a.* 
    FROM table1 AS a
      LEFT JOIN table1 AS q   
        ON  q.id = a.related   
        AND q.type = 0    
    ORDER BY 
        type,
        CASE WHEN id = q.acceptedanswer_id THEN 0 ELSE 1 END,
        timestamp ;


The real issue is the design of the table. The answers should be linked to their related question but they aren't. If there is some way to identify which is answer is related to which question, then the query shoudl be modified to take that into account. Until then, the query (and the table!) is only useful to store one question only and its answers.


As for the question about variables, no you shouldn't use them that way. you may get expected results but it's not safe and may break, either in a future release or due to some optimizer settings change or even because some index was added and a different plan was chosen.



You probably want this order in the results:

ORDER BY 
    related,
    type,
    CASE WHEN id = q.acceptedanswer_id THEN 0 ELSE 1 END,
    timestamp ;