CODEWITHSUNDEEP
sqlsql-server
alteredNate
alteredNate

Reputation: 89

ORDER BY with NULLS first using a column alias

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!)

Upvotes: 1

Views: 374

Answers (2)

PaulStock
PaulStock

Reputation: 11283

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

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453648

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

Upvotes: 3

Related Questions