3

In the following script (see a full demo here):

SELECT *
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
   END
  • What is the type of the 0?
  • How 0 is interpreted? It's not the ordinal number of the columns in the select list, because the select list enumeration begins with 1, not 0. So what is it?

To elaborate a bit, compare the above snippet with the following, which emits an error:

SELECT *
FROM Table1
ORDER BY 0  

The ORDER BY position number 0 is out of range of the number of items in the select list.

And also compare it with the following snippet, which emits another error:

SELECT *
FROM Table1
ORDER BY '0'

A constant expression was encountered in the ORDER BY list, position 1.

It seems that nesting the 0 value under a case clause makes it work, but I don't understand why and how?

  • This question is somewhat a follow-up to this old one, which doesn't explain why it works compared to the alternatives I listed above: How does work SQL with order by 0?
1

3 Answers 3

6

When you use an expression (e.g. CASE) it's no longer sorting by column name or the ordinal of the column, rather it's now going to sort by value. That value you've chosen is 0 in this case (for only non-null Field1 rows), but it could've been 1, 100, 'Red', or even IntFieldSomethingElse + 7.

If you built out your CASE expression a little further, you'd see what I mean:

SELECT *
FROM Table1
ORDER BY 
   CASE 
       WHEN Field1 > 10 
           THEN 100 
        WHEN Field1 > 5
            THEN 200
        WHEN Field1 IS NOT NULL
            THEN 300
        ELSE 900
   END

The above sorts the rows semi-descending based on if the value of Field1 is greater than a certain value. It also has a default case, the ELSE clause, which will put all rows where Field1 IS NULL to the end of the results.

Obviously there's no columns with a name or have an ordinal of 100, 200, 300, or 900 but the above CASE expression works because SQL Server sorts the rows by the values in the THEN clause for the rows that meet that WHEN clause.

The same thing is happening in your example CASE clause when you use 0, it's just sorting non-null rows on the constant value 0. This obviously doesn't mean much in terms of sorting other than all of the non-null rows will be sorted together (since they will all be sorted on the same value of 0 and therefore their order within that sorting will be nondeterministic) and all of the null rows will be sorted together too.

8

The ORDER BY Clause documentation states:

order_by_expression

Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the column in the select list.

The doc could arguably state this more clearly. Per the second sentence, "column" can be specified as:

  • column name
  • column alias
  • column position

CASE is an "expression" rather than a column specification. The expression does not specify a sort order column. The value of expression result is used for sorting.

The result of the expression CASE WHEN Field1 IS NOT NULL THEN 0 END is an integer (due to the zero integer constant) with a value of zero or NULL. The expression type information can be gleaned with sys.dm_exec_describe_first_result_set:

SELECT system_type_name, is_nullable
FROM sys.dm_exec_describe_first_result_set(N'SELECT CASE WHEN Field1 IS NOT NULL THEN 0 END FROM dbo.Table1',NULL,NULL);

+------------------+-------------+
| system_type_name | is_nullable |
+------------------+-------------+
| int              |           1 |
+------------------+-------------+

Using slightly modified data from your fiddle with and a valid CASE expression, you can see that rows with NULL values sort before those with non-null values because the CASE expression returned a NULL for rows with a NULL Field1 value and a zero for rows with non-NULL values.

CREATE TABLE Table1(ID INT IDENTITY(1,1), Field1 INT, FieldSomethingElse VARCHAR(10));
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (1, 'some');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (765, 'or');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (2, 'thing');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'another');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'thing');

SELECT *,CASE WHEN Field1 IS NOT NULL THEN 0 END AS ValueUsedForSorting
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
   END;


+----+--------+--------------------+---------------------+
| ID | Field1 | FieldSomethingElse | ValueUsedForSorting |
+----+--------+--------------------+---------------------+
|  4 | NULL   | another            | NULL                |
|  5 | NULL   | thing              | NULL                |
|  1 | 1      | some               | 0                   |
|  3 | 765    | or                 | 0                   |
|  2 | 2      | thing              | 0                   |
+----+--------+--------------------+---------------------+

I'll add that since NULLs sort before non-NULL values anyway, one could simply order by Field1 to provide the desired sorting behavior and perhaps leverage an index on the column too. Unlike the CASE expression, this method will also sort non-NULL values according the Field1 value rather than the undefined order of the CASE expression zero values (due to ties of zero values).

SELECT *,Field1 AS ValueUsedForSorting
FROM Table1
ORDER BY Field1;

+----+--------+--------------------+---------------------+
| ID | Field1 | FieldSomethingElse | ValueUsedForSorting |
+----+--------+--------------------+---------------------+
|  4 | NULL   | another            | NULL                |
|  5 | NULL   | thing              | NULL                |
|  1 | 1      | some               | 1                   |
|  3 | 2      | thing              | 2                   |
|  2 | 765    | or                 | 765                 |
+----+--------+--------------------+---------------------+
6
  • But how does the value of the expression is used for sorting if there's no column named 0 in Table1, nor ordinal column 0?
    – HeyJude
    Commented Dec 19, 2021 at 11:59
  • @HeyJude, no column is directly used for sorting. It is the result value of the CASE expression.
    – Dan Guzman
    Commented Dec 19, 2021 at 12:31
  • 1
    Sorry, I still don't get it. If no column is directly used for sorting, what is the criteria by which the values are sorted? If the result value of the CASE expression is 0 or null, and given that there's no column 0 (nor null), I don't get how the sorting works. Can you please explain that bit?
    – HeyJude
    Commented Dec 19, 2021 at 12:48
  • @HeyJude, imagine that CASE expression were a column in the SELECT list, and ordering by that column. Just like with ORDER BY a real column that isn't part of the SELECT, you can ORDER BY an expression that isn't part of the SELECT and the data will be sorted by the expression values
    – AMtwo
    Commented Dec 19, 2021 at 13:39
  • @HeyJude, I added more detail and examples to my answer.
    – Dan Guzman
    Commented Dec 19, 2021 at 14:07
0

The order statement generates a 'virtual field' that contains NULL or 0 based on the case condition.

The ORDER BY is applied based on that virtual field.

The zero value in the order by precedes the NULL value. That's all, it's a way to set NULLs of the field1 at the end of the order list.

Try to visualize it in this way:

SELECT *,
CASE WHEN Field1 IS NOT NULL 
     THEN 0 
  END as Filed1Order
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
  END
4
  • Is this a documented thing? Can it be seen in the execution plan?
    – HeyJude
    Commented Dec 19, 2021 at 12:00
  • yes, here: learn.microsoft.com/en-us/sql/t-sql/language-elements/… in the execution plan you will se order by an expression number x ... and if you check the expression code you will find the case statement
    – MBuschi
    Commented Dec 19, 2021 at 12:05
  • The link has a different, simpler case - note that the cases there refers to columns listed in the select list, as opposed to my question.
    – HeyJude
    Commented Dec 19, 2021 at 12:46
  • However it works as I have described ^___^
    – MBuschi
    Commented Dec 19, 2021 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.