Is it possible to ORDER BY a computed column without including it in the result set?

Question

I have this query:

SELECT Column1, Column2, Column3, /* computed column */ AS SortColumn
FROM Table1
ORDER BY SortColumn

SortColumn serves no other purpose as to define an order for sorting the result set. Thus I'd like to omit it in the result set to decrease the size of the data sent to the client. The following fails …

SELECT Column1, Column2, Column3
FROM (
    SELECT Column1, Column2, Column3, /* computed column */ AS SortColumn
    FROM Table1
    ORDER BY SortColumn
) AS SortedTable1

… because of:

Msg 1033, Level 15, State 1

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So there's this hacky solution:

SELECT Column1, Column2, Column3
FROM (
    SELECT TOP /* very high number */ Column1, Column2, Column3, /* computed column */ AS SortColumn
    FROM Table1
    ORDER BY SortColumn
) AS SortedTable1

Is there a clean solution I'm not aware of, since this doesn't sound like a rare scenario?

---

Edit: The solutions already given work indeed fine for the query I referred to. Unfortunately, I left out an important detail: The (already existent) query consists of two SELECTs with a UNION in between, which changes the matter pretty much (again simplified, and hopefully not too simplified):

SELECT Column1, Column2, Column3
FROM Table1
UNION ALL
SELECT Column1, Column2, Column3
FROM Table1
ORDER BY /* computed column */

Msg 104, Level 16, State 1

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

So this error message clearly says that I have to put the computed column in both of the select lists. So there we are again with the subquery solution which doesn't reliably work, as pointed out in the answers.

Answer 1

You don't need to have a computed column in the select statement to use it in an order by

SELECT Column1, Column2, Column3
FROM Table1
ORDER BY /* computed column */

If you need to do it using UNION, then do the UNION in a cte, and the order by in the select, making sure to include all the columns you need to do the calculation in the CTE

WITH src AS (
    SELECT Column1, Column2, Column3, /* computation */ ColumnNeededForOrderBy
    FROM Table1

    UNION ALL

    SELECT Column1, Column2, Column3, /* computation */ ColumnNeededForOrderBy
    FROM Table2
)
SELECT Column1, Column2, Column3
FROM src
ORDER BY ColumnNeededForOrderBy

If you don't care to be specific with the column name, you can use the column index and skip the CTE. I don't like this because you might add a column to the query later and forget to update the index in the ORDER BY clause (I've done it before). Also, the query plans will likely be the same, so it's not like the CTE will cost you anything.

SELECT Column1, Column2, Column3, /* computation */ 
FROM Table1
UNION ALL
SELECT Column1, Column2, Column3, /* computation */ 
FROM Table2
ORDER BY 4

Answer 2

If, for whatever reason, it's not practical to do the calculation in the ORDER BY, you can do something quite similar to your attempt:

SELECT Column1, Column2, Column3
FROM (
    SELECT Column1, Column2, Column3, /* computed column */ AS SortColumn
    FROM Table1
) AS SortedTable1
ORDER BY SortColumn

Note that all that's changed here is that the ORDER BY is applied to the outer query. It's perfectly valid to reference columns in the ORDER BY that don't appear in the SELECT clause.