SQL: case statement in order by clause

问题:

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?

回答1:

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;

回答2:

You can easily visualize why it sorted that way by including the CASE WHEN conditions on SELECT clause:

SELECT BusinessEntityID, SalariedFlag,

CASE SalariedFlag WHEN 1 THEN BusinessEntityID END AS A,
CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END AS B

FROM Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
    ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

Output:

BUSINESSENTITYID  SALARIEDFLAG      A                 B
7                 1                 7                 (null)
5                 1                 5                 (null)
3                 1                 3                 (null)
1                 1                 1                 (null)
2                 0                 (null)            2
4                 0                 (null)            4
6                 0                 (null)            6
8                 0                 (null)            8

The reason the SalariedFlag 0 stay together, is they all fall(look at column A) under null, it would be a different matter altogether if there's another value for SalariedFlag, say number 2, and that query will not work anymore, those with same flag could not necessarily be next each other anymore.

NULL sort first, if you specify DESC, it will sort last.

Live test: http://www.sqlfiddle.com/#!3/1b849/13

---

Recognizing that query's CASE WHEN's THEN uses BusinessEntityID which is of integer type, you can do the following query to achieve the same effect; and since it uses the SalariedFlag as a segregator, the following query might be faster, as you are not using conditionals on ORDER BY. You can just sort of toggle the SalariedFlag and sort on it:

-- just to visualize things up
SELECT BusinessEntityID, SalariedFlag,

    A = BusinessEntityID * SalariedFlag,
    B = BusinessEntityID * (SalariedFlag ^ 1) 

FROM Employee
ORDER BY A DESC, B 

-- in actual you do this:

SELECT BusinessEntityID, SalariedFlag

FROM Employee
ORDER BY BusinessEntityID * SalariedFlag DESC, BusinessEntityID * (SalariedFlag ^ 1) 

Given this data:

CREATE TABLE [Employee]
    ([BusinessEntityID] int, [SalariedFlag] int);

INSERT INTO [Employee]
    ([BusinessEntityID], [SalariedFlag])
VALUES
    (10, 1),
    (5, 1),
    (9, 1),
    (1, 1),
    (2, 0),
    (7, 0),
    (6, 0),
    (8, 0);

This is the output:

BUSINESSENTITYID  SALARIEDFLAG      A                 B
10                1                 10                0
9                 1                 9                 0
5                 1                 5                 0
1                 1                 1                 0
2                 0                 0                 2
6                 0                 0                 6
7                 0                 0                 7
8                 0                 0                 8

Live test: http://www.sqlfiddle.com/#!3/e65ac/2

回答3:

You are ordering first by BusinessEntityID when the flag is 1, and then resolve ties by BusinessEntityID when the flag is 0. This is because the first CASE expression evaluates to null when SalariedFlag is not 1, so all records with the flag of 0 are moved to after the records with the flag of 1.

回答4:

Try including the case statements in the list of values that you are selecting back. It should help to clarify what values the sort is actually occurring on.