Ordering query results

The ORDER BY clause allows you to sort rows within the query results.

<Results ordering>
	|
	ORDER BY <Order conditions>
		|
		<Order field> [<Order>] [, <Order field> [<Order>][, …]]
			|	|
			<Expression>	|
				ASC | DESC | HIERARCHY | HIERARCHY DESC

The ORDER BY clause lists comma-separated conditions used to order query results. Selections are ordered at first by the first condition, then by the second one, and so on.

A general <Order condition> can be an expression. Rows of the query result will be ordered by the value of this expression, calculated for each row.

You can order by ascending or descending values and by hierarchy for tables with hierarchical property (described in Ordering by hierarchy). You can indicate <Order> for each field independently.

You can use logical expressions in this clause.

You can also assign ordering by hierarchy to hierarchical data.

Fields included in ordering conditions do not have to be included in the query result.

Note. A query that contains the DISTINCT keyword and an ORDER BY clause with an expression that is not included in the selection list is treated as invalid. An error message will be displayed. The error message will not be displayed if version 8.1 compatibility mode is enabled.

Example:

// Select five most expensive goods,
// selection must be executed in the descending order of goods price.

SELECT TOP 5
Catalog.Items.Description,
   Catalog.Items.ProcurementPrice

ORDER BY
   Catalog.Items.ProcurementPrice DESC

Query result:

Name	Procurement price
Krups food processor	15 000
Women’s jeans	1 500
"Cowboy" shirt	1 100
Children’s pants	800
"Ultra" mixer	460