|
CHAPTER 2
Single-Table Queries
43
This time, presentation ordering in the output is guaranteed—unlike with queries that don’t have a
presentation ORDER BY clause.
One of the most important points to understand about SQL is that a table has no guaranteed
order, because a table is supposed to represent a set (or multiset, if it has duplicates), and a set has no
order. This means that when you query a table without specifying an ORDER BY clause, the query re-
turns a table result, and SQL Server is free to return the rows in the output in any order. The only way
for you to guarantee that the rows in the result are sorted is to explicitly specify an ORDER BY clause.
However, if you do specify an ORDER BY clause, the result cannot qualify as a table, because the order
of the rows in the result is guaranteed. A query with an ORDER BY clause results in what standard SQL
calls a cursor—a nonrelational result with order guaranteed among rows. You’re probably wondering
why it matters whether a query returns a table result or a cursor. Some language elements and opera-
tions in SQL expect to work with table results of queries and not with cursors; examples include table
expressions and set operators, which I cover in detail in Chapter 5, “Table Expressions,” and in Chapter
6, “Set Operators.”
Notice that the ORDER BY clause refers to the column alias orderyear, which was created in the
SELECT phase. The ORDER BY phase is in fact the only phase in which you can refer to column aliases
created in the SELECT phase, because it is the only phase that is processed after the SELECT phase.
Note that if you define a column alias that is the same as an underlying column name, as in 1 - col1
AS col1, and refer to that alias in the ORDER BY clause, the new column is the one that is considered
for ordering.
When you want to sort by an expression in ascending order, you either specify ASC right after the
expression, as in orderyear ASC, or don’t specify anything after the expression, because ASC is the
default. If you want to sort in descending order, you need to specify DESC after the expression, as in
orderyear DESC.
T-SQL allows you to specify ordinal positions of columns in the ORDER BY clause, based on the
order in which the columns appear in the SELECT list. For example, in the query in Listing 2-4, instead
of using:
ORDER BY empid, orderyear
you could use:
ORDER BY 1, 2
However, this is considered bad programming practice for a couple of reasons. First, in the rela-
tional model, attributes don’t have ordinal positions and need to be referred to by name. Second,
when you make revisions to the SELECT clause, you might forget to make the corresponding revisions
in the ORDER BY clause. When you use column names, your code is safe from this type of mistake.
www.it-ebooks.info
44
Microsoft SQL Server 2012 T-SQL Fundamentals
T-SQL allows you to specify elements in the ORDER BY clause that do not appear in the SELECT
clause, meaning that you can sort by something that you don’t necessarily want to return in the out-
put. For example, the following query sorts the employee rows by hire date without returning the
hiredate attribute.
SELECT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;
However, when DISTINCT is specified, you are restricted in the ORDER BY list only to elements that
appear in the SELECT list. The reasoning behind this restriction is that when DISTINCT is specified, a
single result row might represent multiple source rows; therefore, it might not be clear which of the
multiple possible values in the ORDER BY expression should be used. Consider the following invalid
query.
SELECT DISTINCT country
FROM HR.Employees
ORDER BY empid;
There are nine employees in the Employees table—five from the United States and four from the
United Kingdom. If you omit the invalid ORDER BY clause from this query, you get two rows back—
one for each distinct country. Because each country appears in multiple rows in the source table, and
each such row has a different employee ID, the meaning of ORDER BY empid is not really defined.
Do'stlaringiz bilan baham: | 
