Reputation: 104841
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
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
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
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
Reputation: 131
SELECT *
FROM Data
ORDER BY
Case WHEN @Direction = 1 THEN SortOrder END DESC,
Case WHEN 1=1 THEN SortOrder END
Upvotes: 13
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
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