Sql Server Order By Syntax With Case When And A Constant

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 explai

Solution 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.

Solution 2:

In your first SQL query, the original coder might have meant

ORDERBY3

which means "order by the 3rd column" (which is SupplierId), in ascending order.

In the second query, as @Kokizzo has explained, the author has hard coded the query so that Products from supplierId 2 are at the top, followed by those from supplierId 1, and then all rows from other suppliers. The purpose isn't clear, but for example, this could be a nefarious attempt to promote a certain supplier's products above others e.g. in a web search result page.

The CASE WHEN .. ELSE ... END can be equated to a simple function applied to each row, which takes the supplierId as input and returns the precedence of that row used in the ORDER BY clause.

Solution 3:

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

is equal to (in pseudocode):

ifsupplierId=2thenorder_value=1elseifsupplierId=1thenorder_value=2elseorder_value=3
end 

so the order now is according to the order_value