Question on CASE in Order BY

Question

Hello,

After having read this example -

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName    
FROM Sales.vSalesPerson    
WHERE TerritoryName IS NOT NULL    
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName    
         ELSE CountryRegionName END;    

...I tried to write the Order By Case quiery for my test table and would like to clarify the following:

1

select warehouse, Product, model, quantity

FROM Inventory
Order BY
CASE WHEN warehouse = 'San Fransisco' THEN model
ELSE warehouse END;

2
select warehouse, Product, model, quantity
FROM Inventory
Order BY
CASE WHEN warehouse = 'San Fransisco' THEN quantity
ELSE warehouse END;

As far as I understand this query -

select warehouse, Product, model, quantity  
FROM Inventory  
Order BY   
CASE WHEN warehouse = 'San Fransisco' THEN quantity END,  
CASE WHEN warehouse = 'San Jose' THEN warehouse END;  

• is the same as the query with
  "CASE WHEN warehouse = 'San Fransisco' THEN quantity
  ELSE warehouse END"
  and it does work:
  

Thank you in advance,
Michael

Answer 1

Hi @Mikhail Firsov ,

Order BY CASE WHEN warehouse = 'San Fransisco' THEN model
ELSE warehouse END;

Please refer below Order column in the output, the 'X' row should be the last row since it is after 'S' in order.

Order BY CASE WHEN warehouse = 'San Fransisco' THEN quantity
ELSE warehouse END;

You got the 'Conversion failed when converting the varchar value 'San Jose' to data type int' error due to the quantity and warehouse had different date types. One is integer and the other one is varchar.

You could convert the quantity column as below:

Order BY
CASE WHEN warehouse = 'San Fransisco' THEN quantity END,
CASE WHEN warehouse = 'San Jose' THEN warehouse END;

• is the same as the query with
  "CASE WHEN warehouse = 'San Fransisco' THEN quantity
  ELSE warehouse END"

They are not the same. They had different columns in the output as below:

Please refer below which could be the same:

select warehouse, Product, model, quantity  
FROM Inventory  
order by CASE WHEN warehouse = 'San Fransisco' THEN cast(quantity as char)   
ELSE warehouse END   
  
select warehouse, Product, model, quantity  
FROM Inventory  
order by   
CASE WHEN warehouse = 'San Fransisco' THEN cast(quantity as char)  
     WHEN warehouse = 'San Jose' THEN warehouse END   

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

P.S. I don't know why the pictures are not displayed on the page :(

Answer 3

Are they the same - no. Do they do the same thing for your specific query - possibly. In your first query you are have 2 different order by clauses.

Order BY
CASE WHEN warehouse = 'San Fransisco' THEN quantity END,
CASE WHEN warehouse = 'San Jose' THEN warehouse END;

The first order by orders by quantity when warehouse is SF otherwise it does nothing. Then for those that have the same first value (basically SF vs everyone else) then it orders by warehouse if SJ otherwise it does nothing. Without testing I suspect you'll see SF rows ordered by quantity first and then SJ rows ordered by warehouse and then everything else.

In the second query you have a single order by.

CASE WHEN warehouse = 'San Fransisco' THEN quantity
ELSE warehouse END"

The rows are ordered by either quantity or warehouse depending upon the warehouse value. Since quantity and warehouse are both used the single ordering will order all rows using the textual value of one of two columns. If numbers sort higher in the collation used by the DB then SF quantities will appear first otherwise the warehouse will sort first and the SF quantities last.

Answer 4

"Are they the same - no. Do they do the same thing for your specific query - possibly. ", - yes, I agree, but why this

CASE WHEN warehouse = 'San Fransisco' THEN quantity
ELSE warehouse END"

returns the the error (please see q03.png)???

Answer 5

Let's start with the original:

ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName  
          ELSE CountryRegionName END;

The idea here seems to be that we equate US states with countries. Which sort of make sense, since many independent states are in the size of a US state.

But when you are playing around, it's gets funky, because a warehouse and model are not really related.

When it comes to

CASE WHEN warehouse = 'San Fransisco' THEN quantity
ELSE warehouse END

This fails because you are mixing data types. SQL is a statically typed language, and a CASE expression always return the one and same data type. When two types in SQL Server meet, SQL Server applies a strict precedence list, and in this list, varchar ranks fairly low, only binary trails behind. integer is higher on the list, so thus SQL Server attempts to convert the warehouse name to number, and this ends in tears.

When you have two CASE expression as in

CASE WHEN warehouse = 'San Fransisco' THEN quantity END,
CASE WHEN warehouse = 'San Jose' THEN warehouse END;

It runs without error. But whether it's meaningful? Nah.