SQL: case statement in order by clause

Question

http://msdn.microsoft.com/en-us/library/ms181765.aspx I see the sql below from above link: ``` SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END; GO ``` Here is one result I get: BusinessEntityID,SalariedFlag 7,1 5,1 3,1 1,1 2,0 4,0 6,0 8,0 Could anyone explain why the records with same salariedFlag are next to each other and why salariedFlag=1 chunk is above the salariedFlag=0 chunk?

Accepted Answer

The sort clause is equivalent to the following, which may be slightly more obvious:

```
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID ELSE null END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID ELSE null END;
```

So the first sort field is the BusinessEntityID when SalariedFlag = 1, or null. That will group all the rows where SalariedFlag = 0 together as they all have a null first sort field. The rows that SalariedFlag = 1 wil be sorted by BusinessEntityID. It looks like nulls get sorted last in a descending sort so all the SalariedFlag != 1 go last.

That's the major sort, for the secondary sort, much the same thing happens: All the rows where SalariedFlag = 0 will be sorted by BusinessEntityID. Since their primary sort fields were all null, they will end up ordered by BusinessEntityID.

And all the rows where SalariedFlag != 0 will be grouped together with a null secondary ordering. If those rows had SalariedFlag = 1, then they would already have been sorted by the primary ordering.

If SalariedFlag can only be 0 or 1 then this sort can be (slightly) simplified to:

```
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        , BusinessEntityID;
```

SQL: case statement in order by clause

Solution

Problem