How To Use Case Function In Order By?

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.

Solution 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:

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

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

Solution 2:

Given the table definition (without proper indices) + sample data

CREATETABLE Table1
    (`Id` int, `QuestionOrAnswer` varchar(9), `Type` int, `AcceptedAnswerId` varchar(4), `related` intNOTNULL, `timestamp` int)
;

INSERTINTO Table1
    (`Id`, `QuestionOrAnswer`, `Type`, `AcceptedAnswerId`, `related`, `timestamp`)
VALUES
    (1, 'question1', 0, '3', 1, 1),
    (2, 'answer1', 1, NULL, 1, 2),
    (3, 'answer2', 1, NULL, 1, 3),
    (4, 'answer3', 1, NULL, 1, 4)

you can use the query

SELECT
  t2.*
FROM
  table1 as t1
JOIN
  table1 as t2
 ON
   t1.related=t2.related
 WHERE
   t1.related = 1AND t1.Type = 0ORDERBY
   t2.Type desc, t2.Id=t1.AcceptedAnswerId, t2.Id

to get the question/answer set of a specific question (t1.related = 1 <- adjust that parameter for other questions). And no, with the right indices this query is not "expensive".

example at http://sqlfiddle.com/#!9/24954/4 (yeah, took me 4 attempts to get it right, grrrrr)