SQL Custom Order By

Question

I can't understand why this doesn't work:

select distinct a.QuestionID,a.QuestionName,b.AnswerID,b.AnswerName
    from @TempExportList a
    join tblAnswers b
    on a.QuestionID = b.QuestionID
    where a.PaperID=@PaperID
    order by (case when a.QuestionName='A' then 0
                   when a.QuestionName='B' then 1
                   else a.QuestionID
              end)

I get the following error -

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

But this works fine:

select distinct a.QuestionID,a.QuestionName,b.AnswerID,b.AnswerName
    from @TempExportList a
    join tblAnswers b
    on a.QuestionID = b.QuestionID
    where a.PaperID=@PaperID
    order by a.QuestionID

Answer 1

The error message explains the problem perfectly.

In the first example the ORDER BY item -- CASE WHEN ... END -- doesn't appear in the SELECT list.

In the second example the ORDER BY item -- a.QuestionID -- does appear in the SELECT list.

To fix the first example you'll need to do something like this:

SELECT DISTINCT a.QuestionID, a.QuestionName, b.AnswerID, b.AnswerName,
                CASE WHEN a.QuestionName = 'A' THEN 0
                     WHEN a.QuestionName = 'B' THEN 1
                     ELSE a.QuestionID
                END
FROM @TempExportList AS a
    JOIN tblAnswers AS b
        ON a.QuestionID = b.QuestionID
WHERE a.PaperID = @PaperID
ORDER BY CASE WHEN a.QuestionName = 'A' THEN 0
              WHEN a.QuestionName = 'B' THEN 1
              ELSE a.QuestionID
         END

Answer 2

You can get around this with a CTE

;WITH T AS
(
SELECT DISTINCT a.QuestionID,a.QuestionName,b.AnswerID,b.AnswerName
    FROM @TempExportList a
    JOIN tblAnswers b
    ON a.QuestionID = b.QuestionID
    WHERE a.PaperID=@PaperID
)
SELECT   *
FROM     T
ORDER BY
         CASE
                  WHEN QuestionName='A'
                  THEN 0
                  WHEN QuestionName='B'
                  THEN 1
                  ELSE QuestionID
         END