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
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
.
ORDER BY
clause has the following behavior:
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.RETURN Results AS SELECT SUM(Price) AS Total FROM WineState 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.
Geocode sorting
Expression sorting
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 FROM SaleState; RETURN T2 AS SELECT ... AS bar FROM T1 ORDER BY T1.foo /* not allowed */ /* Valid statement */ DEFINE T1 AS SELECT ... AS foo FROM SaleState; 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 */
Sorting by sets
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 FROM WineState 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).
Stability of ORDER BY
EQL guarantees that the results of a statement are stable across queries. This means that:
ORDER BY
clause is specified, or there are ties in the order specified in the ORDER BY
clause.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.
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:
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.