SELECT - ORDER BY¶
Sorts data returned by a query in Querona. Use this clause to:
- Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
- Determine the order in which ranking function values are applied to the result set.
ORDER BY order_by_expression [ ASC | DESC ] [ ,...n ]
Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name or column alias, or a non-negative integer representing the position of the column in the select list.
Multiple sort columns can be specified. Column names must be unique. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. That is, the result set is sorted by the first column and then that ordered list is sorted by the second column, and so on.
The column names referenced in the ORDER BY clause must correspond to either a column in the select list or to a column defined in a table specified in the FROM clause without any ambiguities.
ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.
Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. For example, although a statement such as SELECT ProductID, Name FROM Production.Production ORDER BY 2 is valid, the statement is not as easily understood by others compared with specifying the actual column name. In addition, changes to the select list, such as changing the column order or adding new columns, requires modifying the ORDER BY clause in order to avoid unexpected results.
In a SELECT TOP (N) statement, always use an ORDER BY clause. This is the only way to predictably indicate which rows are affected by TOP. For more information, see TOP.
Examples in this section demonstrate the basic functionality of the ORDER BY clause using the minimum required syntax.
A. Specifying a single column defined in the select list¶
The following example orders the result set by the numeric
Because a specific sort order is not specified, the default (ascending order) is used.
USE AdventureWorks2012; SELECT ProductID, Name FROM Production.Product WHERE Name LIKE 'Lock Washer%' ORDER BY ProductID;
B. Specifying a column that is not defined in the select list¶
The following example orders the result set by a column that is not included in the select list, but is defined in the table specified in the FROM clause.
USE AdventureWorks2012; SELECT ProductID, Name, Color FROM Production.Product ORDER BY ListPrice;
C. Specifying an alias as the sort column¶
The following example specifies the column alias
SchemaName as the sort order column.
USE AdventureWorks2012; SELECT name, SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects WHERE type = 'U' ORDER BY SchemaName;
D. Specifying an expression as the sort column¶
The following example uses an expression as the sort column. The expression is defined by using the DATEPART function to sort the result set by the year in which employees were hired.
USE AdventureWorks2012; SELECT BusinessEntityID, JobTitle, HireDate FROM HumanResources.Employee ORDER BY DATEPART(year, HireDate);
E. Specifying both ascending and descending order¶
The following example orders the result set by two columns.
The query result set is first sorted in ascending order by the
and then sorted in descending order by the
USE AdventureWorks2012; SELECT LastName, FirstName FROM Person.Person WHERE LastName LIKE 'R%' ORDER BY FirstName ASC, LastName DESC ;
F. Specifying a conditional order¶
The following examples use the CASE expression in an ORDER BY clause to conditionally determine the sort order of the rows based on a given column value.
In the first example, the value in the
SalariedFlag column of the
HumanResources.Employee table is evaluated.
Employees that have the
SalariedFlag set to 1 are returned in order by the
BusinessEntityID in descending order.
Employees that have the
SalariedFlag set to 0 are returned in order by the
BusinessEntityID in ascending order.
In the second example, the result set is ordered by the column
TerritoryName when the column
CountryRegionName is equal to ‘United States’
CountryRegionName for all other rows.
SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC , CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName ELSE CountryRegionName END;
G. Using ORDER BY in a ranking function¶
The following example uses the ORDER BY clause in the ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE.
USE AdventureWorks2012; SELECT p.FirstName, p.LastName , ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number" , RANK() OVER (ORDER BY a.PostalCode) AS "Rank" , DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank" , NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile" , s.SalesYTD, a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
H. Using ORDER BY with UNION, EXCEPT, and INTERSECT¶
When a query uses the UNION, EXCEPT, or INTERSECT operators, the ORDER BY clause must be specified at the end of the statement
and the results of the combined queries are sorted. The following example returns all products that are red or yellow
and sorts this combined list by the column
USE AdventureWorks2012; SELECT Name, Color, ListPrice FROM Production.Product WHERE Color = 'Red' -- ORDER BY cannot be specified here. UNION ALL SELECT Name, Color, ListPrice FROM Production.Product WHERE Color = 'Yellow' ORDER BY ListPrice ASC;