Search code examples
sqlsql-serversql-order-by

MS Sql: Conditional ORDER BY ASC/DESC Question

I want to to make to make the ordering in my query conditional so if it satisfiess the condition it should be ordered by descending

For instance:

SELECT * FROM Data ORDER BY SortOrder CASE WHEN @Direction = 1 THEN DESC END
Solution

  • Don't change the ASC or DESC, change the sign of the thing being sorted-by:

    SELECT * FROM table 
ORDER BY 
CASE WHEN @Direction = 1 THEN -id else id END asc;

    The OP asks:

    Guys, I am not the SQL Expert, please explain me what means the id and -id, does it controls the ordering direction?

    id is just whatever column you're sorting by; -id is just the negation of that, id * -1. If you're sorting by more than one column, you'll need to negate each column:

    SELECT * FROM table 
ORDER BY 
CASE WHEN @Direction = 1 THEN -id else id END 
CASE WHEN @Direction = 1 THEN -othercolumn else othercolumn END ;

    If you're ordering by a non numeric column, you'll need to find an expression that makes that column "negative"; writing a function to do that may help.