Is it possible to ORDER BY a computed column witho…

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

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.

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

Advertisement

Answer