ORDER BY clause

ORDER BY clause

order-by-clause:

ORDER BY ordering-term [, ordering-term]*

ordering-term:

expression [ASC | DESC]

The ORDER BY clause lets your order the result set based on an expression. The order of items in the result set is determined by the expression specified in this clause. Objects are sorted first by the left-most expression in the list of expressions. Any items with the same sort value will be sorted with the next expression in the list. This process repeats until all items are sorted and all expressions in the list are evaluated. You can specify whether to order the results in ascending or descending order.

If no ORDER BY clause is specified, the order in which the result objects are returned is undefined.

As ORDER BY expressions can evaluate to any JSON value, we define an ordering when comparing values of different types. The following list describes the order by type (from lowest to highest):

  • missing value
  • null
  • false
  • true
  • number
  • string (string comparison is done using a raw byte collation of UTF8 encoded strings)
  • array (element by element comparison is performed until the end of the shorter array; if all the elements so far are equal, then the longer array sorts after)
  • object (larger objects sort after; for objects of equal length, key/value by key/value comparison is performed; keys are examined in sorted order using the normal ordering for strings)

The following example shows the ORDER BY clause.

SELECT fname, age
      FROM tutorial
          ORDER BY age 

Returns:


 {
   "results": [
     {
       "age": 18,
       "fname": "Fred"
     },
     {
       "age": 20,
       "fname": "Harry"
     },
     {
       "age": 40,
       "fname": "Jane"
     },
     {
       "age": 46,
       "fname": "Dave"
     }
   ]
 }