ORDER BY with NULLS first using a column alias

Question

Although this technique works great for standard columns, I have run into something I don't understand. I am rewriting results in my query for reporting purposes and need to order by the aliased column 'PARENT', like so:

select fun.MODULE_ID
    , fun.FUNCTION_ID
    , COALESCE(fun.parent_function, fun2.function_id) as PARENT
    , fun.DESCRIPTION
    , fun.FUNCTION_PURPOSE
from MODULE_FUNCTION fun
LEFT JOIN MODULE_FUNCTION fun2
    ON fun.function_id = fun2.function_id
    AND fun2.function_id IN (SELECT parent_function FROM MODULE_FUNCTION)
ORDER BY MODULE_ID
    , PARENT
    , FUNCTION_ID

This works great, but my column 'PARENT_FUNCTION' and its alias 'PARENT' contain nulls. I would like these to be at the bottom. So when I do this:

ORDER BY MODULE_ID
    , case when PARENT is null then 1 else 0 end, PARENT
    , FUNCTION_ID

I get " Invalid column name 'PARENT'. "

Any ideas? (Still very new with SQL!)

Please see http://dba.stackexchange.com/questions/21965/why-are-queries-parsed-in-such-a-way-that-disallows-the-use-of-column-aliases-in and http://dba.stackexchange.com/questions/19762/why-is-the-select-clause-listed-first/ — Aaron Bertrand, Dec 14 '12 at 22:10

score 3 · Accepted Answer · edited Dec 14 '12 at 22:23

3

SQL Server won't let you use a column alias elsewhere in the query except in an ORDER BY clause (but not in an expression within ORDER BY), so you have to retype the original column definition. For a more in depth explanation of this (much better than I could give) please see this page

try this:

ORDER by MODULE_ID
   , case when COALESCE(fun.parent_function, fun2.function_id) is null then 1 else 0 end
   , FUNCTION_ID

edited Dec 14 '12 at 22:23

Aaron Bertrand

272,866
37
466
490

answered Dec 14 '12 at 21:54

PaulStock

11,053
9
49
52

1

Please explain why he had to repeat the expression – John Saunders Dec 14 '12 at 21:57
It works!! Thanks! But I agree, this is REALLY not logical to me, seems like if it can order by the alias to begin with, why couldn't it use the same alias with some logic? – alteredNate Dec 14 '12 at 21:59
When using a column alias in an `ORDER BY` clause it can only be used on its own. If you try and use it in an expression SQL Server will no longer try and resolve it as an alias and will try and resolve it to a column in one of the base tables instead. – Martin Smith Dec 14 '12 at 22:00
I see there are going to be some idiosyncrasies on the path to learning SQL... Thanks in any case for the explination, I won't ask "why" :) – alteredNate Dec 14 '12 at 22:04
@JohnSaunders, good suggestion. I edited my answer to try and explain why expression needed to be repeated. – PaulStock Dec 14 '12 at 22:06
I've edited the answer to make it more correct (@Martin feel free to roll it back, I saw your post after I submitted). – Aaron Bertrand Dec 14 '12 at 22:24
@AaronBertrand - No need to roll back! – Martin Smith Dec 14 '12 at 22:25
2

@AaronBertrand, thank you so much for correcting my completely muddled and wrong explanation. I have learned (and continue to learn) so much from you regarding SQL. AlteredNate, if you want to learn SQL, I highly suggest following Aaaron's blog. – PaulStock Dec 14 '12 at 22:28

score 3 · Answer 2 · answered Dec 14 '12 at 22:23

When using a column alias in an ORDER BY clause it can only be used on its own. If you try and use it in an expression SQL Server will try and resolve it to a column in one of the base tables instead.

For example

DECLARE @T TABLE(X INT, Y int)

INSERT INTO @T 
VALUES (1,3),(2,2),(3,1)

SELECT X AS Y
FROM @T 
ORDER BY Y

Y
-----------
1
2
3

SELECT X AS Y
FROM @T 
ORDER BY Y + 0

Y
-----------
3
2
1

The first result set is ordered by the column alias and the second by the column Y in the base table.

You can either repeat the underlying expression as in the other answer or use a derived table / cte to project the column then you can use that projected column in an expression in an order by.

;WITH CTE
     AS (SELECT fun.MODULE_ID,
                fun.FUNCTION_ID,
                COALESCE(fun.parent_function, fun2.function_id) AS PARENT,
                fun.DESCRIPTION,
                fun.FUNCTION_PURPOSE
         FROM   MODULE_FUNCTION fun
                LEFT JOIN MODULE_FUNCTION fun2
                  ON fun.function_id = fun2.function_id
                     AND fun2.function_id IN (SELECT parent_function
                                              FROM   MODULE_FUNCTION))
SELECT *
FROM   CTE
ORDER  BY MODULE_ID,
          CASE
            WHEN PARENT IS NULL THEN 1
            ELSE 0
          END,
          PARENT,
          FUNCTION_ID

Martin, thank you so much for your in-depth explanation. Have up-voted your answer since it is much better than my own. — PaulStock, Dec 14 '12 at 22:30
@PaulStock - Thanks! This order by behavior is definitely far from intuitive IMO. — Martin Smith, Dec 14 '12 at 22:32
@martin-smith Indeed, thank you for that, and I think I'll explore the CTE option. My next research topic! — alteredNate, Dec 14 '12 at 22:34

ORDER BY with NULLS first using a column alias

2 Answers2