ORDER BY clause

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.

Syntax

ORDER BY { column-Name | ColumnPosition }
    [ ASC | DESC ]
    [ , column-Name | ColumnPosition
        [ ASC | DESC ] ] * 

ColumnPosition is an integer that identifies the number of the column in the SelectItem in the underlying Query of the SELECT statement. ColumnPosition 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 in the select list.

column-Name refers to the names visible from the SelectItems in the underlying query of the SELECT statement. An order by column does not need to be in the select list.

ASC specifies that the results should be returned in ascending order; DESC specifies that the results should be returned in descending order. If the order is not specified, ASC is the default.

An ORDER BY clause prevents a SELECT statement from being an updatable cursor. (For more information, see Requirements for updatable cursors and updatable ResultSets.)

For example, if an INTEGER column contains integers, NULL is considered greater than 1 for purposes of sorting. In other words, NULL values are sorted high.

-- order by the correlation name NATION
-- order by the correlation name NATION
SELECT CITY_NAME, COUNTRY AS NATION 
FROM CITIES 
ORDER BY NATION;
Related concepts
Interaction with the dependency system
CREATE statements
DROP Statements
RENAME statements
SET statements
Related reference
ALTER TABLE statement
CALL (PROCEDURE)
CONSTRAINT clause
DECLARE GLOBAL TEMPORARY TABLE statement
DELETE statement
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
INNER JOIN
INSERT statement
JOIN operation
LEFT OUTER JOIN
LOCK TABLE statement
Query
RIGHT OUTER JOIN
ScalarSubquery
SelectExpression
SELECT statement
TableExpression
TableSubquery
UPDATE statement
VALUES expression
WHERE clause
WHERE CURRENT OF clause