CODEWITHSUNDEEP
sql-serversortingsql-order-by
Jonysuise
Jonysuise

Reputation: 1830

Sorting a query, how does thas it work?

Can someone explain to me why this is possible with SQL Server :

select column1 c,column2 d
from table1
order by c,column3

I can sort by column1 using the alias because order by clause is applied after the select clause, but how is it possible to sort by a column that i'm not retreiving ?

Thanks in advance.

Upvotes: 0

Views: 74

Answers (3)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

All column names from the objects in the FROM clause are available to ORDER BY, except in the case of GROUPing or DISTINCT. As you've indicated the alias is also available, because the SELECT statement is processed before the ORDER BY.

This is one of those cases where you trust the optimizer.

Upvotes: 2

Elias
Elias

Reputation: 2632

You can sort by alias' which you define in the select select column1 c and then you tell it to sort by a column that you are not including in the select, but one that still exists in the table. This allows us to sort by expressions of data, without having to have it in the select.

Select cost, tax From table ORDER BY (cost*tax)

Upvotes: 0

user121489
user121489

Reputation:

According to Books Online (http://technet.microsoft.com/en-us/library/ms188385(v=sql.90).aspx)

The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

Additionally, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

Upvotes: 1

Related Questions