CODEWITHSUNDEEP
sqlsql-serversql-order-by
Shimmy Weitzhandler
Shimmy Weitzhandler

Reputation: 104841

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

Upvotes: 28

Views: 41611

Answers (6)

DTripodi
DTripodi

Reputation: 129

I really like the idea of using a window function, I definitely think it's the most fun solution, but that is an unnecessary amount of heavy lifting.

If it is a numeric value and you can just say use the negative value for descending that is great, but that is definitely not always the case.

My solution for this is to sort by both ascending and descending but to set the sorted value NULL for the one you want to ignore.

In this way, you will sort by NULL (which will do no sorting at all) in the order you want to ignore:

SELECT * 
    FROM Schema.Data 
        ORDER BY CASE WHEN @Direction = 0 THEN Data.SortOrder END
            , CASE WHEN @Direction = 1 THEN Data.SortOrder END DESC

To clarify why this is NULL and doesn't affect the sort order when the CASE is not true, it is important to know that the default ELSE value for a CASE statment is NULL.

Upvotes: 1

GBGOLC
GBGOLC

Reputation: 600

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]

Upvotes: 0

LarsW
LarsW

Reputation: 131

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

Upvotes: 13

Jeremy Giaco
Jeremy Giaco

Reputation: 131

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

Upvotes: 13

tpdi
tpdi

Reputation: 35181

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.

Upvotes: 51

MikeW
MikeW

Reputation: 5922

I have done something like this

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

Upvotes: 1

Related Questions