ORDER BY clauses

The ORDER BY clause is used to control the order of result records.

The ORDER BY syntax is as follows:

ORDER BY <AttrName> [ASC/DESC] [,<AttrName> [ASC/DESC]]*

You can sort result records by any of their attribute values, and can specify whether to sort in ascending (ASC) or descending (DESC) order. You can use any combination of values and sort orders. The absence of a direction, as with Attr3 in the example above, implies ASC.

When an ORDER BY clause is used, NULL values will always sort after non-NULL values for a given attribute, and NaN (not-a-number) values will always sort after values other than NaN and NULL, regardless of the direction of the sort. Tied ranges (or all records in the absence of an ORDER BY clause) are ordered in an arbitrary but stable way: the same query will always return its results in the same order, as long as it is querying against the same version of the data. Data updates add or remove records from the order, but will not change the order of unmodified records.

In this example, the amount is calculated for each sales representative. The resulting records are sorted by total amount in descending order:

DEFINE Reps AS
SELECT SUM(Amount) AS Total
GROUP BY SalesRep
ORDER BY Total DESC

Geocode sorting

Data of type geocode is sorted by latitude and then by longitude. To establish a more meaningful sort order when using geocode data, compute the distance from some point and then sort by the distance.

String sorting

String values are sorted in Unicode byte order.

Stability of ORDER BY

EQL guarantees that the results of a statement are stable across queries. This means that:

If no updates are performed, then the same statement will return results in the same order on repeated queries, even if no ORDER BY clause is specified, or there are ties in the order specified in the ORDER BY clause.
If updates are performed, then only changes that explicitly impact the order will impact the order; the order will not be otherwise affected. Changes that impact the order are things like deleting or inserting records that contribute to the result on or prior to the returned page, or modifying a value that is used for grouping or ordering.

For example, on a statement with no ORDER BY clause, queries that use PAGE(0, 10), then PAGE(10, 10), then PAGE(20, 10) will, with no updates, return successive groups of 10 records from the same arbitrary but stable result.

For an example with updates, on a statement with ORDER BY Num PAGE(3, 4), an initial query returns records {5, 6, 7, 8}. An update then inserts a record with 4 (before the specified page), deletes the record with 6 (on the specified page), and inserts a record with 9 (after the specified page). The results of the same query, after the update, would be {4, 5, 7, 8}. This is because:

The insertion of 4 shifts all subsequent results down by one. Offsetting by 3 records includes the new record.
The removal of 6 shifts all subsequent results up by one.
The insertion of 9 does not impact any of the records prior to or included in this result.