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.
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
ProductID column. Because a specific sort order is not specified, the default (ascending order) is used.
SELECT ProductID, 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.
SELECT ProductID, Name, Color
ORDER BY ListPrice;
C. Specifying an alias as the sort column
The following example specifies the column alias
SchemaName as the sort order column.
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.
SELECT BusinessEntityID, JobTitle, HireDate
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
SELECT LastName, FirstName
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.
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
, CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
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.
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
JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
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
SELECT Name, Color, ListPrice
WHERE Color = 'Red'-- ORDER BY cannot be specified here.
UNION ALLSELECT Name, Color, ListPrice
FROM Production.Product WHERE Color = 'Yellow'
ORDER BY ListPrice ASC;