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;

ORDER BY with multiple columns

If more than one column is specified in an ORDER BY clause, rows are first arranged by the values in the first column. Then rows that contain the same first-column value are arranged according to the values in the second column, and so on. Each ORDER BY column can include its own sort order specification.

Important:
In multi-column sorts, after a sort order is specified, it applies to all subsequent columns until another sort order is specified, as in the previous example. This attribute is sometimes called sticky sort order. For example, the following cursor declaration orders retrieval by LAST_NAME in descending order, then refines it alphabetically within LAST_NAME groups by FIRST_NAME in ascending order:

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 ASC;

Collation Order in an ORDER BY Clause

When CHAR or VARCHAR columns are ordered in a SELECT statement, it can be necessary to specify a collation order for the ordering, especially if columns used for ordering use different collation orders.

To specify the collation order to use for ordering a column in the ORDER BY clause, include a COLLATE clause after the column name. For example, in the following ORDER BY clause, a different collation order for each of two columns is specified:

. . .
ORDER BY LNAME COLLATE FR_CA, FNAME COLLATE FR_FR;

For more information about collation order and a list of available collations in InterBase, see the Data Definition Guide.

Enhancements to ORDER BY

InterBase 2017 Update 1 introduces enhancements to the ORDER BY syntax. For more information refer to: Enhancements to GROUP BY and ORDER BY.

Advance To:

• Grouping Rows with GROUP BY