volume one volume one - 7 months ago 17
SQL Question

Case statement for Order By clause with Desc/Asc sort

SELECT *
FROM
TableName
WHERE
ORDER BY
CASE @OrderByColumn
WHEN 1 THEN Forename
WHEN 2 THEN Surname
END;


I have a statement like above which lets me dynamically choose how to order the results of a query. However, how do I specify that I want the Forename ordered
DESC
and the Surname
ASC
?

Answer

You need to split your ORDER BY in two parts:

SELECT *
FROM
    TableName
WHERE
ORDER BY 
    (CASE @OrderByColumn
    WHEN 1 THEN Forename
    END) DESC -- Forename --> descending
,   (CASE @OrderByColumn
    WHEN 2 THEN Surname
    END) ASC -- Surname --> ascending