Sorting Rows with ORDER BY
Go Up to Understanding Data Retrieval with SELECT
By default, a query retrieves rows in the exact order it finds them in a table, and because internal table storage is unordered, retrieval, too, is likely to be unordered. To specify the order in which rows are returned by a query, use the optional ORDER BY clause at the end of a SELECT statement.
ORDER BY retrieves rows based on a column list. Every column in the ORDER BY clause must also appear somewhere in the SELECT clause at the start of the statement. Each column can optionally be ordered in ascending order (ASC, the default), or descending order (DESC). The complete syntax of ORDER BY is:
ORDER BY col [COLLATE collation] [ASC | DESC] [,col [COLLATE collation] [ASC | DESC] ...];
For example, the following cursor declaration orders output based on the LAST_NAME column. Because DESC is specified in the ORDER BY clause, employees are retrieved from Z to A:
EXEC SQL DECLARE PHONE_LIST CURSOR FOR SELECT LAST_NAME, FIRST_NAME, PHONE_EXT FROM EMPLOYEE WHERE PHONE_EXT IS NOT NULL ORDER BY LAST_NAME DESC, FIRST_NAME;