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.

Syntax#

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

Arguments#

order_by_expression

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.

Best Practices#

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#

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 ProductID column. 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 FirstName column and then sorted in descending order by the LastName column.

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’ and by 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 ListPrice.

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;

SELECT - ORDER BY

Contents