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
?
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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With