CODEWITHSUNDEEP
sqlsql-serversql-order-by
MrTex
MrTex

Reputation: 249

SQL Server : ORDER BY CASE ignored

I have a SQL statement with ORDER BY CASE something like this:

SELECT *
FROM PRODUCTS
ORDER BY
    CASE 
        WHEN StockQuantity > 0 THEN 1
        WHEN OutOfStockOrder = 1 THEN 1
        ELSE 0
    END, _Ranking DESC, Name, ProductId

Records are ordered correctly with CASE; the first records with 1(StockQuantity > 0 OR OutOfStockOrder = 1) then record with 0. Also, records with CASE 1 (StockQuantity > 0 OR OutOfStockOrder = 1) are then ordered correctly with _Ranking, then Name and finally ProductId. But, when CASE are 0, _Ranking are not used by order; records are ordered only for Name and then ProductId.

Like this:

ProductId | Name     | StockQuantity | OutOfStockOrder | _Ranking
----------+----------+---------------+-----------------+-----------
15        | Camera   |  10           | 0               | 50
25        | Mouse    |  5            | 1               | 25
10        | Keyboard |  1            | 0               | 5
50        | Ball     |  0            | 0               | 0
60        | IPad     |  0            | 0               | 35

Is there something I missed?

I'm using SQL Server 2017.

Upvotes: 1

Views: 161

Answers (1)

forpas
forpas

Reputation: 164139

Fix your CASE expression so that rows with StockQuantity > 0 OR OutOfStockOrder = 1 are returned first.
The rest of the query is correct:

SELECT *
FROM PRODUCTS
ORDER BY CASE 
           WHEN StockQuantity > 0 OR OutOfStockOrder = 1 THEN 0
           ELSE 1
         END, 
         _Ranking DESC,
         Name, 
         ProductId

See the demo.
Results:

ProductId Name StockQuantity OutOfStockOrder _Ranking
15 Camera 10 0 50
25 Mouse 5 1 25
10 Keyboard 1 0 5
60 IPad 0 0 35
50 Ball 0 0 0

Upvotes: 1

Related Questions