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:

  • Is Sql Server's ISNULL() function lazy/short-circuited?
  • SQL How to check if field value is >0 (case when)?
  • Update Top 1 record in table sql server
  • SQL Server hogging CPU and memory - caused by system clock change
  • Stored Procedure Call from Lightswitch
  • How to optimise SQL table for read speed for point to point distance data
  • SELECT * FROM Data ORDER BY SortOrder CASE WHEN @Direction = 1 THEN DESC END

  • Running SUM in T-SQL
  • NOLOCK vs. Transaction Isolation Level
  • sql server 2008: combining two complex queries
  • replace or remove characters and then cast to decimal
  • SQL Server/Oracle table writable only by a trigger?
  • 4 Solutions collect form web for “MS Sql: Conditional ORDER BY ASC/DESC Question”

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

    SELECT * FROM table 
    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 
    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.

    SELECT * 
    FROM Data 
    Case WHEN @Direction = 1 THEN SortOrder END DESC, 
    Case WHEN 1=1 THEN SortOrder END

    You can also use a scheme which supports all column types:

    SELECT <column_list> FROM <table>
    CASE WHEN @sort_order = 'ASC' AND @sort_column = '<column>' THEN <column> END ASC,
    CASE WHEN @sort_order = 'DESC' AND @sort_column = '<column>' THEN <column> END DESC

    I have done something like this

    select productId, InventoryCount, 
        when @Direction = 1 then InventoryCount 
        else -InventoryCount 
        end as "SortOrder"
    order by 3
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.