I have one question that confusing me.

By logical query processing, the SELECT clause comes before the ORDER BY clause, and in theory, every step in a query creates a virtual table. So, how is it even possible to order result by a column that is not listed in the SELECT clause, when the SELECT clause generates a virtual table with the selected columns?

In case your query has a GROUP BY clause, then the behavior become normal, but in the absence of a GROUP BY clause, it seems a little tricky. Does anybody have an explanation?

  • 4
    Imagine that the SELECT step produces a virtual table that includes also some "shadow" columns/expressions (all those that are in the ORDER BY clause but not in the SELECT list). These shadow columns can be used for the ordering but are not included in the final results. Commented Mar 28, 2013 at 7:07
  • @ypercube: this should be an answer...
    – gbn
    Commented Mar 28, 2013 at 10:21
  • @ypercube Please, post your comment as an answer, and add some additional explanation, of course, if you had
    – veljasije
    Commented Mar 28, 2013 at 11:12

2 Answers 2


The column specified in the ORDER BY is always retrieved from its underlying table even the specified column is not specified in the select list. An example to show the behavior,

Execute the T-SQL below and include action execution plan,

USE AdventureWorks;

SELECT Title, LastName
FROM Person.Person
WHERE PersonType = 'EM'
ORDER BY FirstName;

In execution plan, the output list of the clustered index scan operator shows 'Title', 'FirstName', and 'LastName'. Then the result set is sorted in the sort operator before returning the ordered result with the specified select column in select operator.

  • How you explain using column aliases from select list in ORDER BY clause, if ORDER BY was first executed?
    – veljasije
    Commented Mar 28, 2013 at 19:21
  • The columns, including ORDER BY column (table column) are read from the table FIRST, then sorted, then returned only the result set with columns that are specified in the SELECT clause. In case of the column alias (e.g. LastName + ' Jr.' AS JrName and ORDER BY JrName), the sequence of the execution plan is read data from the table, compute scalar (compute JrName), order by and return result set.
    – Travis
    Commented Mar 29, 2013 at 2:34

If you have something in your ORDER BY clauses then it will be included in the relevant internal structures as if they were in the SELECT list - this definitely counts for base fields not otherwise being output by the statement and may count for computed values too. It just doesn't output these "extra" columns in the final stage.

If it didn't include the columns from the ORDER BY clause in those internal structures, it would have to requery the source tables at the final state to retrieve the ordering data which would be far far less efficient in the vast majority of cases. The only time this might make sense is if you are ordering by the result of a correlated sub-query that the query planner can't map to something less inconvenient, in that case you want it to do that part last once it knows which rows are being output (no point running a complex sub-query for many rows that are going to be filtered out by other clauses later when it can just run it for the few rows being output).

Not the answer you're looking for? Browse other questions tagged or ask your own question.