ORDER BY


You can use the ORDER BY clause to specify a sort order for a result set. Limitations are that you cannot use the ORDER BY clause in views, inline functions, derived tables, and subqueries, unless TOP is also specified. The ORDER BY syntax is as follows:

 [ ORDER BY {order_by_expression [ ASC | DESC ] } [ ,...n ] ]

The order_by_expression placeholder specifies the column on which to sort. The sort column can be specified as a name or column alias (which can be qualified by the table or view name); an expression; or a nonnegative integer representing the position of the name, alias, or expression in the select list. Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set.

The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list. Furthermore, when the SELECT statement includes a UNION operator, the column names or column aliases must be those specified in the first select list.

Note 

You cannot use columns of type varchar(max), nvarchar(max), or varbinary(max) in an ORDER BY clause.

The ASC and DESC (ascending, descending) arguments are used as follows:

  • ASC   The ASC argument directs the SELECT that the values in the specified column be sorted in ascending order, from lowest value to highest value.

  • DESC   The DESC argument directs that the values in the specified column be sorted in descending order, from highest value to lowest value. NULL values are treated as the lowest possible values.

There is no limit to the number of items in the ORDER BY clause (n). However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause. In our short example, if we now add the ORDER BY as follows:

 SELECT Item, Quantity, Category FROM Items   HAVING Category LIKE 'Animals'   ORDER BY Quantity

the following result set is returned ordered on the Quantity column with ASC as the default:

Item

Quantity

Category

Monkeys

342

Animals

Apes

3434

Animals




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net