SQL Server order by syntax with Case When and a Constant

Question

I'm reading TSQL code someone else wrote and find a somewhat weird syntax. It's doing order by a string. I did some test and the following is the code. Anyone can help me to explain it? Thanks.

First Query

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's Cajun Seasoning    2           2           48 - 6 oz jars       22.00                 53           0            0            0
...

*/

Second query:

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

Result:

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

Third Query

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's Cajun Seasoning             2           2           48 - 6 oz jars       22.00                 53           0            0            0
5           Chef Anton's 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's Boysenberry Spread             3           2           12 - 8 oz jars       25.00                 120          0            25           0
7           Uncle Bob's 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" has to be able to translate each row into a value, then those values can be compared. "Order by '3'" doesn't make any sense as a useful query, as it's not using the row - hence the error message of ordering by a constant expression.

"Order by (some expression returning a string)" makes perfect sense. I would personally have used numbers rather than strings, but fundamentally it's still just ordering by a value.

Would you have found it odd to see "order by ProductName"? That's ordering by a string too.

Hopefully that helps - it's not really clear which bit was causing a problem though.