10.4. Order Clauses

Order clauses are for reordering tuples.

For each incoming tuple, the expression in the where clause is evaluated to an atomic. The tuples are then sorted based on the atomics they are associated with, and then forwarded to the next clause.

Like for ordering comparisons, null values are always considered the smallest.

The following query is the counterpart of SQL's "SELECT a.display_name, a.score FROM answers a ORDER BY a.display_name".

Example 10.10. An order by clause.

for $answer in collection("answers")
order by $answer.owner.display_name
return {
  "owner" : $answer.owner.display_name,
  "score" : $answer.score
}

Results:

{
  "owner" : "JasonSmith",
  "score" : 34
}
{
  "owner" : "JasonSmith",
  "score" : 6
}
{
  "owner" : "JasonSmith",
  "score" : 0
}
{
  "owner" : "JasonSmith",
  "score" : 1
}
{
  "owner" : "Rob Wells",
  "score" : 4
}
{
  "owner" : "Ubiguchi",
  "score" : 7
}
{
  "owner" : "Victor Nicollet",
  "score" : 17
}
{
  "owner" : "descent89",
  "score" : 1
}

Multiple sorting criteria can be given - they are treated with the semantics of a lexicographic order, that is, incoming tuples are first sorted according to the first criterion, and in case of equality the second criterion is used, etc.

Example 10.11. An order by clause with two criteria.

for $answer in collection("answers")
order by $answer.owner.display_name,
         $answer.score
return {
  "owner" : $answer.owner.display_name,
  "score" : $answer.score
}

Results:

{
  "owner" : "JasonSmith",
  "score" : 0
}
{
  "owner" : "JasonSmith",
  "score" : 1
}
{
  "owner" : "JasonSmith",
  "score" : 6
}
{
  "owner" : "JasonSmith",
  "score" : 34
}
{
  "owner" : "Rob Wells",
  "score" : 4
}
{
  "owner" : "Ubiguchi",
  "score" : 7
}
{
  "owner" : "Victor Nicollet",
  "score" : 17
}
{
  "owner" : "descent89",
  "score" : 1
}

For each criterion, it can be specified whether the order is ascending or descending. Empty sequences are allowed and it can be chosen whether to put them first (even before null) or last (even after null).

Example 10.12. An order by clause with ordering options.

for $answer in collection("answers")
order by $answer.owner.display_name
             descending empty greatest,
         $answer.score ascending
return {
  "owner" : $answer.owner.display_name,
  "score" : $answer.score
}

Results:

{
  "owner" : "descent89",
  "score" : 1
}
{
  "owner" : "Victor Nicollet",
  "score" : 17
}
{
  "owner" : "Ubiguchi",
  "score" : 7
}
{
  "owner" : "Rob Wells",
  "score" : 4
}
{
  "owner" : "JasonSmith",
  "score" : 0
}
{
  "owner" : "JasonSmith",
  "score" : 1
}
{
  "owner" : "JasonSmith",
  "score" : 6
}
{
  "owner" : "JasonSmith",
  "score" : 34
}

An error is raised if the expression does not evaluate to an atomic or to the empty sequence.