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.
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.
RETURN Results AS SELECT SUM(Price) AS Total GROUP BY WineType ORDER BY Total DESC
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 values are sorted in Unicode code point order.
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.
/* 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 */
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 */
As mentioned above, an arbitrary but stable order is used when sorting by sets (multi-assign attributes).
RETURN Results AS SELECT SET(Price) AS PriceSet GROUP BY WineType ORDER BY PriceSet DESC
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).
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.
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.