SQL Server order syntax with Case When and constant

Question

I am reading the TSQL code that someone wrote and found some strange syntax. It performs line order. I did some tests, and the following code. Can anyone help me explain this? Thanks.

First request

SELECT * FROM dbo.Products 

Result:

 ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued ----------- ------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ 1 Chai 1 1 10 boxes x 20 bags 18.00 39 0 10 0 2 Chang 1 1 24 - 12 oz bottles 19.00 17 40 25 0 3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00 13 70 25 0 4 Chef Anton Cajun Seasoning 2 2 48 - 6 oz jars 22.00 53 0 0 0 ... */ 

Second request:

 SELECT * FROM dbo.Products WHERE ProductID < 10 ORDER BY '3'; 

Result:

Msg 408, Level 16, State 1, Line 1 A constant expression was found in the ORDER BY list, position 1.

Third request

 SELECT * FROM dbo.Products WHERE ProductID < 10 ORDER BY CASE WHEN SupplierID = 2 THEN '1' WHEN SupplierID = 1 THEN '2' ELSE '3' END; 

Result:

 ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued ----------- ---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ 4 Chef Anton Cajun Seasoning 2 2 48 - 6 oz jars 22.00 53 0 0 0 5 Chef Anton Gumbo Mix 2 2 36 boxes 21.35 0 0 0 1 1 Chai 1 1 10 boxes x 20 bags 18.00 39 0 10 0 2 Chang 1 1 24 - 12 oz bottles 19.00 17 40 25 0 3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00 13 70 25 0 6 Grandma Boysenberry Spread 3 2 12 - 8 oz jars 25.00 120 0 25 0 7 Uncle Bob Organic Dried Pears 3 7 12 - 1 lb pkgs. 30.00 15 0 10 0 8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.00 6 0 0 0 9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.00 29 0 0 1 (9 row(s) affected) */ 

Answer 1

"Order by" should be able to translate each line into a value, then these values ​​can be compared. "Order by" 3 "does not make any sense as a useful query because it does not use a string - hence, an error message about ordering with a constant expression.

"Sort by (some expression that returns a string)" makes sense. I would personally use numbers, not strings, but in principle it was still just sorted by value.

Would you find it strange to see "order by ProductName"? This ordering is also a string.

Hope this helps - it's actually not entirely clear which bit is causing the problem.

Answer 2

In your first SQL query, the source encoder could mean

 ORDER BY 3 

which means "3rd column order" (which is equal to SupplierId ) in ascending order.

In the second request, as @Kokizzo explained, the author hard-coded the request so that the products from SupplierId 2 are at the top, and then those from SupplierId 1, and then all the lines from other suppliers.The purpose is not clear, but, for example, it could be a vile attempt promote certain suppliers' products above others, for example. on the web search results page.

CASE WHEN .. ELSE ... END can be equated to a simple function applied to each row that accepts the SupplierId input and returns the priority of this row used in the ORDER BY .

Answer 3

 CASE WHEN SupplierID = 2 THEN '1' WHEN SupplierID = 1 THEN '2' ELSE '3' END 

equals (in pseudo-code):

 if supplierId = 2 then order_value = 1 else if supplierId = 1 then order_value = 2 else order_value = 3 end 

so the order now matches the order of value