ORDER BY Clause

Specify the order in which rows appear in the ResultSet.

Syntax

ORDER BY { column-name | column-position | expression }
    [ ASC | DESC ]
    [ , column-name | column-position | expression
        [ ASC | DESC ] ] * 

Description

The ORDER BY clause is an optional element of a SELECT statement. An ORDER BY clause allows you to specify the order in which rows appear in the ResultSet.
Note: You cannot use the ORDER BY clause in a query used to create a view. See CREATE VIEW.
Note: Aggregates (set functions) are not supported in the ORDER BY clause. However, for some queries you can reference the column-position of an aggregate function from the projected columns of the SELECT statement to include the aggregate in ORDER BY. See the example in column-position.

GemFire XD does not support using ORDER BY on a column of datatype BLOB, CLOB, or LONG VARCHAR FOR BIT DATA.

column-name

Refers to the names visible from the select items in the underlying query of the SELECT statement. The column-Name that you specify in the ORDER BY clause does not need to be the SELECT list.

column-position

An integer that identifies the number of the column in the select items in the underlying query of the SELECT statement. column-position must be greater than 0 and not greater than the number of columns in the result table. In other words, if you want to order by a column, that column must be specified in the SELECT list.

Although GemFire XD does not support including Aggregates (set functions) in the ORDER BY clause, you can use the column-position to reference an existing aggregate in the projected columns of the SELECT statement. For example, in the following query the MAX(SALESDATE) aggregate is included in the ORDER BY by using its column position of 2:
gfxd> SELECT SALESNUMBER, MAX(SALESDATE) from SALES
     GROUP BY SALESNUMBER
     ORDER BY 2 DESC
     OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

expression

A sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression.

ASC

Specifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.

DESC

Specifies that the results should be returned in descending order.

Note: If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list.
Note: An ORDER BY clause prevents a SELECT statement from being an updatable cursor.