Understanding a Sql Server Query - CASE within an ORDER BY clause

Question

I'm trying to mess around with putting CASE statement in the ORDER BY clause using a DISTINCT in the SELECT list, and finding some odd behavior that I don't understand. Here is some code:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'c' then BU
else ISO_ID
end

This works. But if I change the 4th line to when 'b' = 'b' then BU:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'b' then BU
else ISO_ID
end

it breaks with error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

When BU is obviously in the select list. Even stranger is when I change the code to:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'b' then BU
else BU   --change is here
end

It works again! How does that even make sense? Can someone help me wrap my brain around this one?

Answer 1

The rules for CASE are that the result should be cast to the datatype of the branch with highest precedence.

For the first query it uses contradiction detection and just generates a plan that sorts by ISO_ID directly. This is numeric already so no need to implicitly cast and so matches the expression in the select list with no problem.

For the second query it can again determine at compile time that it needs to ORDER BY BU. Except it actually needs to ORDER BY CAST(BU AS NUMERIC) due to the above. This means it would need to ORDER BY a computed expression not matching anything anything in the SELECT list. Hence the problem.

Your third query removes the expression of higher precedence from the CASE thus removing the need for an implicit cast (and hence the need to order by a computed expression).

As the computed expression is entirely dependant upon the columns in the SELECT DISTINCT list however you could rewrite the second query as follows.

;WITH CTE AS
(
SELECT DISTINCT Requester,
                ISO_ID              AS ISO,
                ( ISO_ID - 5 + 50 ) AS 'someNum',
                BU
FROM   LoanerHeader
)
SELECT *
FROM CTE
ORDER  BY CASE
            WHEN 'a' = 'b' THEN Requester
            WHEN 'b' = 'b' THEN BU
            ELSE ISO
          END