Why and How do ORDER BY CASE Queries Work in SQL Server?

Question

Let's look at the following table:


| col1     | col2           |
| -------- | -------------- |
| 1        | NULL           |
| 23       | c              |
| 73       | NULL           |
| 43       | a              |
| 3        | d              |

Suppose you wanted to sort it like this:

| col1     | col2           |
| -------- | -------------- |
| 1        | NULL           |
| 73       | NULL           |
| 43       | a              |
| 23       | c              |
| 3        | d              |

With the following code this would be almost trivial:

SELECT *
FROM dbo.table1
ORDER BY col2;

However, to sort it in the following, non-standard way isn't that easy:

| col1     | col2           |
| -------- | -------------- |
| 43       | a              |
| 23       | c              |
| 3        | d              |
| 1        | NULL           |
| 73       | NULL           |

I made it with the following code

SELECT *
FROM dbo.table1
ORDER BY CASE WHEN col2 IS NULL THEN 1 ELSE 0 END, col2;

Can you explain to me 1) why and 2) how this query works? What bugs me is that the CASE-statement returns either 1 or 0 which means that either ORDER BY 1, col2 or ORDER BY 0, col2 will be executed. But the following code gives me an error:

SELECT *
FROM dbo.table1
ORDER BY 0, col2;

Yet, the overall statement works. Why?

Ordering by a number (ordinal ordering) is ordering by the column in that ordinal position **not** the value 0. But column number starts from 1, hence why order by 0 fails. Its an old fashioned, unrecommended way of ordering. — Dale K, Mar 30 '21 at 20:52
Note that `CASE` is an expression so SQL orders by that value. However, with the constant expression, the ordinal of the column in the SELECT clause is used. — Dan Guzman, Mar 30 '21 at 20:57
Does this answer your question? [What is the purpose of Order By 1 in SQL select statement?](https://stackoverflow.com/questions/3445118/what-is-the-purpose-of-order-by-1-in-sql-select-statement) — Dale K, Mar 30 '21 at 20:57
@DaleK what `ORDER BY 1` accomplishes is clear to me. I don't understand why the CASE-statement can return 0, but `ORDER BY 0, col2` throws an error. — Moritz Wolff, Mar 30 '21 at 21:02
Because its not longer treated as ordinal ordering when you add a `case` *expression* (or any calculation). — Dale K, Mar 30 '21 at 21:04
@DaleK How is it treated then? This is exactly my problem because I was assuming it would be treated like ordinal ordering. — Moritz Wolff, Mar 30 '21 at 21:08
Its treated as natural datatype i.e. int. If you think about it `ORDER BY 0` has the same value for every row. `ORDER BY CASE WHEN col2 IS NULL THEN 1 ELSE 0 END` does not (necessarily) have the same value for every row. Some rows can be 0 while others can be 1, so you are ordering by an int (desc or asc as desired). — Dale K, Mar 30 '21 at 21:12

score 3 · Accepted Answer · answered Mar 30 '21 at 20:53

3

How does this work?

ORDER BY (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END),
         col2;

Well, it works exactly as the code specifies. The first key for the ORDER BY takes on the values of 1 and 0 based on col2. The 1 is only when the value is NULL. Because 1 > 0, these are sorted after the non-NULL values. So, all non-NULL values are first and then all NULL values.

How are the non-NULL values sorted? That is where the second key comes in. They are ordered by col2.

answered Mar 30 '21 at 20:53

Gordon Linoff

1,242,037
58
646
786

I wasn't aware that the 1's and 0's returned by CASE are not referring to columns any more when executed likes this. With this is mind your answer makes sense. – Moritz Wolff Mar 30 '21 at 21:59
1

@MoritzWolff From [`Order By`](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15): "_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." Note that _only_ a nonnegative integer will be interpreted as a positional column reference. Neither `case` nor any other (non-degenerate) expression will be treated as a position. Also see Best Practices on that page. – HABO Mar 31 '21 at 03:17
@HABO Yes, it's not treated as a position, but it's not specified in the documentation of ORDER BY how it works combined with CASE exactly. Do you have any idea where I could find this information? It's a bit weird that the only place I found information about this is Stackoverflow. – Moritz Wolff Mar 31 '21 at 10:35
1

@MoritzWolff . . . It is in the documentation for `order by`. But it can be a bit hard to follow, especially if English is not your native language. The positional arguments to `order by` are only available when they are *not* part of expressions. – Gordon Linoff Mar 31 '21 at 12:40
@MoritzWolff "[A] nonnegative integer" means a _literal_ value (aka an [integer constant](https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver15#integer-constants) with a nonnegative value. No, it's not obvious from the text. Imagine what would happen if anytime you tried to sort on an integer column (or expression) that every row decided how it wanted to be sorted by picking sort columns on the fly. Make it recursive if the column turns out to be another integer and ... . That just isn't going to work. – HABO Mar 31 '21 at 13:24
@MoritzWolff Aside: My reference to "non-degenerate" expression is because `1` _is_ an [expression](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/expressions-transact-sql?view=sql-server-ver15), though of the simplest form: `constant`. A more complex expression is `( expression )`. I consider it a bug in SQL Server that it treats `order by 1` and `order by (1)` as the same, but `order by (1+0)` results in an error ("A constant expression was encountered in the ORDER BY list, position 1."). `(1)` is clearly an _expression_ and should result in the same error. – HABO Mar 31 '21 at 13:31

score 1 · Answer 2 · answered Mar 30 '21 at 21:55

Starting with this sample data:

--==== Sample Data
DECLARE @t TABLE (col1 INT, col2 VARCHAR(10))
INSERT @t(col1,col2) VALUES (1,NULL),(23,'c'),(73,NULL),(43,'a'),(3 ,'d');

Now note these three queries that do the exact same thing.

--==== QUERY1: Note the derived query
SELECT t.col1, t.col2
FROM
(
  SELECT t.col1, t.col2, SortBy = CASE WHEN col2 IS NULL THEN 1 ELSE 0 END
  FROM   @t AS t
) AS t
ORDER BY t.SortBy;

--==== QUERY2: This does the same thing but with less code
SELECT   t.col1, t.col2, SortBy = CASE WHEN col2 IS NULL THEN 1 ELSE 0 END
FROM     @t AS t
ORDER BY SortBy;

--==== QUERY3: This is QUERY2 simplified
SELECT   t.col1, t.col2
FROM     @t AS t
ORDER BY CASE WHEN col2 IS NULL THEN 1 ELSE 0 END;

Note that you can simplify your CASE statements like so:

--==== Simplified Case statemnt examples
SELECT   t.col1, t.col2
FROM     @t AS t
ORDER BY CASE col2 WHEN NULL THEN 1 ELSE 0 END;

SELECT   t.col1, t.col2
FROM     @t AS t
ORDER BY IIF(col2 IS NULL,1,0);

score 1 · Answer 3 · answered Aug 20 '22 at 10:29

This is a description for oracle database SQL's ORDER BY:

ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
  [, { expr | position | c_alias }
     [ ASC | DESC ]
     [ NULLS FIRST | NULLS LAST ]
  ]...

We can see that position and expr were depicted as separate paths in the diagram. From the fact, we can conclude that the 0 and 1 are not categorized as position because the CASE expression is not position even though the expression would be evaluated to a number, which is can be viewed as position value.

I think this view can be applied to T-SQL too.

score 0 · Answer 4 · answered Mar 31 '21 at 02:14

Try this:

DECLARE     @Table TABLE (col1 int, col2 char(1))
INSERT INTO @Table
    VALUES  
            ( 1 , NULL)
        ,   ( 23, 'c'   )
        ,   ( 73, NULL)
        ,   ( 43, 'a'   )
        ,   ( 3 , 'd'   )
;

SELECT *
FROM @Table
ORDER BY ISNULL(col2, CHAR(255))

score 0 · Answer 5 · answered Mar 31 '21 at 16:46

Common table expressions can be a big help both as a way of clarifying an issue as well as solving it. If you move the CASE clause up into the CTE and then use it to sort, this answers both why and how it works.

With Qry1 (
    SELECT      col1, 
                col2, 
                CASE WHEN col2 IS NULL THEN 1 ELSE 0 END As SortKey
    FROM        dbo.table1
)
SELECT      *
FROM        Qry1
ORDER BY    SortKey, col2;

Why and How do ORDER BY CASE Queries Work in SQL Server?

5 Answers5