# How does order by zero work

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?

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.

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                 |
+----+--------+--------------------+---------------------+
``````
• 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? Commented Dec 19, 2021 at 11:59
• @HeyJude, no column is directly used for sorting. It is the result value of the CASE expression. Commented Dec 19, 2021 at 12:31
• 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? 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 Commented Dec 19, 2021 at 13:39
• @HeyJude, I added more detail and examples to my answer. Commented Dec 19, 2021 at 14:07

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
``````
• Is this a documented thing? Can it be seen in the execution plan? 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 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. Commented Dec 19, 2021 at 12:46
• However it works as I have described ^___^ Commented Dec 19, 2021 at 12:49