Reputation: 7583
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
?
Upvotes: 17
Views: 45391
Reputation: 291
another example:
SELECT * FROM dbo.Employee
ORDER BY
CASE WHEN Gender='Male' THEN EmployeeName END Desc,
CASE WHEN Gender='Female' THEN Country END ASC
more details ...http://codechef4u.com/post/2015/04/07/order-by-clause-with-case-expressions-case-statement
Upvotes: 4
Reputation: 727137
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
Upvotes: 29
Reputation: 1271231
You need two clauses in the order by
:
ORDER BY (CASE WHEN @OrderByColumn = 1 and @Dir = 'ASC' THEN Forename
WHEN @OrderByColumn = 2 and @Dir = 'ASC' THEN Surname
END) ASC,
(CASE WHEN @OrderByColumn = 1 and @Dir = 'DESC' THEN Forename
WHEN @OrderByColumn = 2 and @Dir = 'DESC' THEN Surname
END) DESC
Upvotes: 8