MS Sql: Conditional ORDER BY ASC/DESC Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

SELECT <column_list> FROM <table> ORDER BY 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

Answer 4

I have done something like this

select productId, InventoryCount, 
    case 
    when @Direction = 1 then InventoryCount 
    else -InventoryCount 
    end as "SortOrder"
order by 3

Answer 5

Dynamic sorting in either ASC or DESC order, irrespective of datatype.

The first example sorts alphabetically, the second using numbers. The @direction variable denotes sort direction (0 = ASC or 1 = DESC) and [column] is the sort column.

This also works for multi-column sorting and you can hide the [row] column if placed in a further outer query.

DECLARE @direction BIT = 1 -- 0 = ASC or 1 = DESC

-- Text sort. 

SELECT 
    IIF(@direction = 0, ROW_NUMBER() OVER (ORDER BY [column] ASC), ROW_NUMBER() OVER (ORDER BY [column] DESC)) [row]
    , *
FROM
(   -- your dataset.
    SELECT N'B' [column] 
    UNION SELECT N'C' 
    UNION SELECT N'A'
) [data] ORDER BY [row] 

-- Numeric sort.

SELECT 
IIF(@direction = 0, ROW_NUMBER() OVER (ORDER BY [column] ASC), ROW_NUMBER() OVER (ORDER BY [column] DESC)) [row],
*
FROM
(   -- your dataset.
    SELECT 2 [column] 
    UNION SELECT 3 
    UNION SELECT 1
) [data] ORDER BY [row]