CODEWITHSUNDEEP
sqlsql-servert-sql
volume one
volume one

Reputation: 7583

Case expression 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?

Upvotes: 17

Views: 45391

Answers (3)

nagnath
nagnath

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

Sergey Kalinichenko
Sergey Kalinichenko

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

Gordon Linoff
Gordon Linoff

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

Related Questions