SQL Server – ORDER BY Clause on Column Not in SELECT List

sql server

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created step before.

But, it is possible to order result set by column that is not listed in select list.

How is it possible, when SELECT clause are executed before ORDER BY and which primary task is to select columns for rendering?

It is a little confusing or I don't get something.

Best Answer

It's not always possible:

If DISTINCT is specified, the expression in the ORDER BY clause have access only to the virtual table returned by the previous step (VT5)

(VT5 is virtual table returned generated by SELECT)

If DISTINCT is not specified expressions in the ORDER BY clause can access both the input and the output virtual tables of the SELECT phase.

Explanation is taken from "Inside SQLServer 2008 T-SQL Querying" - I'm not sure if this particular chapter is available online (Step 6: The Presentation ORDER BY Phase, p.16.)

Thus,

SELECT distinct field2 FROM table1 order by field1

gives an error:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Related Question