mysql - How to use CASE function in ORDER BY?

Question

My friend asked a question a few times ago. Also there is a answer under that and it is good, but not for my case. The idea of that solution is joining the current table to itself. That seems expensive and not effective for me, Because in reality there is four join on these tables (votes, favorites, comments, viewed) in my query.

Now I want to know, how can I do that using CASE function? Something like this:

... ORDER BY Type, CASE WHEN AcceptedAnswerId = Id THEN 1 ELSE 0, timestamp

Or is there any better solution?

---

To be more readable, I paste those examples here:

I have a table like this:

// Mytable
+----+--------------------+------+------------------+-----------+
| Id |  QuestionOrAnswer  | Type | AcceptedAnswerId | timestamp |
+----+--------------------+------+------------------+-----------+
| 1  | question1          | 0    | 3                | 1         |
| 2  | answer1            | 1    | NULL             | 2         |
| 3  | answer2            | 1    | NULL             | 3         | -- accepted answer
| 4  | answer3            | 1    | NULL             | 4         |
+----+--------------------+------+------------------+-----------+

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

+----+--------------------+------+------------------+-----------+
| Id |  QuestionOrAnswer  | Type | AcceptedAnswerId | timestamp |
+----+--------------------+------+------------------+-----------+
| 1  | question1          | 0    | 3                | 1         |
| 3  | answer2            | 1    | NULL             | 3         | -- accepted answer 
| 2  | answer1            | 1    | NULL             | 2         |
| 4  | answer3            | 1    | NULL             | 4         |
+----+--------------------+------+------------------+-----------+
//                          ^ 0 means question and 1 means answer

Answer 1

CASE would work, but you are missing the END. But in this case, you could also just use IF(AcceptedAnswerId = Id,1,0).

In the simple case you show, you could just do:

order by type,if(type=0,(@accepted:=acceptedanswerid),id<>@accepted),timestamp

but I don't know if that would work in your real case.