135
Chapter 6: Introducing Basic Query Flow
6
An alias is allowed in the ORDER BY clause but not the WHERE clause because the WHERE
clause is logically executed prior to processing columns and expressions. The ORDER BY
clause follows the assembling of the columns and aliases, so it can use column aliases.Using the Column Ordinal Position
You can use the ordinal number of the column (column position number) to indicate the
ORDER BY columns, but don’t do this. If the select columns are changed or their order
changes, the sort order also changes.One case for which it’s not necessarily a horrid practice to use the ordinal number to spec-
ify the sort is for complex union queries (see Chapter 9).The following query demonstrates sorting by ordinal position:USE AdventureWorks
SELECT LastName + ', ' + FirstName as FullName
FROM Person.Person
ORDER BY 1 ;Result:FullName
----------------------
Abbas, Syed
Abel, Catherine
Abercrombie, Kim...
ORDER BY and Collation
SQL Server’s collation order is vital to sorting data. In addition to determining the
alphabet, the collation order also determines whether accents, case, and other alphabet
properties are considered in the sort order. For example, if the collation is case-sensitive,
then the uppercase letters are sorted before the lowercase letters. The following function
reports the installed collation options and the current collation server property:SELECT * FROM fn_helpcollations();Result:name description
--------------------- -------------------------
Albanian_BIN Albanian, binary sort
Albanian_CI_AI Albanian, case-insensitive,
accent-insensitive,
kanatype-insensitive, width-insensitive
Albanian_CI_AI_WS Albanian, case-insensitive,
accent-insensitive,c06.indd 135c06.indd 135 7/30/2012 4:16:06 PM7/30/2012 4:16:06 PM
http://www.it-ebooks.info