ORDER BY clauses

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

You can sort result records by specifying attribute names or an arbitrary expression.

The ORDER BY syntax is as follows:
ORDER BY <Attr|Exp> [ASC|DESC] [,<Attr|Exp> [ASC|DESC]]*

where Attr|Exp is either an attribute name or an arbitrary expression. The attribute can be either a single-assign or multi-assign attribute.

Optionally, you 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 implies ASC.

An ORDER BY clause has the following behavior:
In this example, the Price single-assign attribute is totaled and then grouped by the single-assign WineType attribute. The resulting records are sorted by the total amount in descending order:
RETURN Results AS
SELECT SUM(Price) AS Total
GROUP BY WineType
ORDER BY Total DESC
The result of this statement from a small set of twenty-five records might be:
Total      WineType
---------------------------
| 142.34 | Red            |
| 97.97  | White          |
| 52.90  | Chardonnay     |
| 46.98  | Brut           |
| 25.99  | Merlot         |
| 21.99  | Bordeaux       |
| 16.99  | Blanc de Noirs |
| 14.99  | Pinot Noir     |
|        | Zinfandel      |
---------------------------

The Zinfandel bucket is sorted last because it has a NULL value for Price. Note that if the sort order were ASC, Zinfandel would still be last in the result.

String sorting

String values are sorted in Unicode code point order.

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.

Expression sorting

An ORDER BY clause allows you to use an arbitrary expression to sort the resulting records. The expressions in the ORDER BY clause will only be able to refer to attributes of the local statement, except through lookup expressions, as shown in these simple statements:
/* Invalid statement */
DEFINE T1 AS
SELECT ... AS foo

RETURN T2 AS
SELECT ... AS bar
FROM T1
ORDER BY T1.foo  /* not allowed */

/* Valid statement */
DEFINE T1 AS
SELECT ... AS foo

RETURN T2 AS
SELECT ... AS bar
FROM T1
ORDER BY T1[].foo  /* allowed */
In addition, the expression cannot contain aggregation functions. For example:
RETURN T AS
SELECT ... AS bar
FROM T1
ORDER BY SUM(bar) /* not allowed because of SUM aggregation function */

RETURN T AS
SELECT ... AS bar
FROM T1
ORDER BY ABS(bar) /* allowed */

Sorting by sets

As mentioned above, an arbitrary but stable order is used when sorting by sets (multi-assign attributes).

In this example, the Price single-assign attribute is converted to a set and then grouped by the single-assign WineType attribute. The resulting records are sorted by the set in descending order:
RETURN Results AS
SELECT SET(Price) AS PriceSet
GROUP BY WineType
ORDER BY PriceSet DESC
The result of this statement from a small set of 25 records might be:
PriceSet                                              WineType
----------------------------------------------------------------------
| { 14.99 }                                         | Pinot Noir     |
| { 12.99, 13.95, 17.5, 18.99, 19.99, 21.99, 9.99 } | Red            |
| { 25.99}                                          | Merlot         |
| { 22.99, 23.99 }                                  | Brut           |
| { 21.99 }                                         | Bordeaux       |
| { 20.99, 32.99, 43.99 }                           | White          |
| { 16.99 }                                         | Blanc de Noirs |
| { 17.95, 34.95 }                                  | Chardonnay     |
|                                                   | Zinfandel      |
----------------------------------------------------------------------

In this descending order, the Zinfandel bucket is sorted last because it does not have a Price assignment (and thus returns an empty set).

Stability of ORDER BY

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

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.

Note that ORDER BY only impacts the result of a RETURN clause, or the effect of a PAGE clause. ORDER BY on a DEFINE with no PAGE clause has no effect.