Sorts data returned by a query in Lyftrondata. 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 ALLSELECT Name, Color, ListPrice
FROM Production.Product WHERE Color = 'Yellow'
ORDER BY ListPrice ASC;
See Also