CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
KathyBlue
KathyBlue

Reputation: 366

TSQL Dynamic ordering of values

I have a dynamic query with a dynamic sorting over time filter that would create a descending sort order of the product items per value which in most of the cases is the expected.:

ROW_NUMBER() OVER (PARTITION BY T.Time_Calculation ORDER BY SUM(T.[Displayed Value 1]) DESC) +2 end AS DisplayItem_SortOrder

Now the catch: I have a dataset type (this is set by a field in the WHERE clause) where negative numbers also come, and in this case I need to order from highest negative (ASC) to highest positive float datatype values.

The ABS() doesn't resolve this because then some positive numbers appear in the middle. I tried to add a CASE statement but it seems that "ASC" and "DESC" is not something one can add inside the same. Any suggestions would be appreciated.

Display Name    DisplayItem_SortOrder   Displayed Value 1
Product1    3   -31454.61396
Product10   4   -3407.16712
Product2    5   -708.9942251
Product3    6   123.5454381
Product6    7   428.6027531
Product5    8   454.4814295
Product4    9   454.9838374
Product9    10  455.54
Product8    11  471.54

Upvotes: 0

Views: 62

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

I think you are looking for something like this:

Create and populate sample data (Please save us this step in your future questions)

DECLARE @T AS Table
(
    col int
)

INSERT INTO @T VALUES
(1), (2), (3), (4), (-1), (-2), (-3), (5)

The query:

SELECT col
FROM @T
ORDER BY CASE WHEN col < 0 THEN 0 ELSE 1 END, 
         ABS(col)

Results: 

col
-1
-2
-3
1
2
3
4
5

Upvotes: 4

Related Questions