ORDER BY clause

The ORDER BY clause sorts the result-set by one or more columns, in ascending or descending order.

Purpose

In a SELECT statement, the ORDER BY clause sorts the result-set in ascending or descending order, based on one or more fields or expressions of those fields in the projection.

Prerequisites

For you to select data from a document or keyspace, you must have the query_select privilege on the document or keyspace. For more details about user roles, see Authorization.

Syntax

In the below SELECT statement, the ORDER BY clause is boldfaced:

SELECT select_clause
[FROM from_clause] [JOIN join_clause]
[USE INDEX useindex_clause]
[LET let_clause]
[WHERE where_clause ( [AND where_clause2] )* ]
[GROUP BY groupby_clause] [LETTING|HAVING letting_clause]
[UNION|INTERSECT|EXCEPT union_clause]
[ORDER BY orderby_clause]
[LIMIT limit_int]
[OFFSET offset_clause]
;

Syntax of the orderby_clause

ORDER BYexpr [ASC|DESC] [, expr2 [ASC|DESC] ]*

Arguments

expr: [Required] This identifier or expression can be document fields, new expressions, or an alias in the SELECT clause.
ASC | DESC: [Optional; default is ASC]

ASC orders in ascending order.

DESC orders in descending order.

Return Values

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

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.

When a field has a mix of data types, the different JSON types are sorted in the following order (from lowest to highest):

MISSING
NULL (including JSON 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 longer arrays sort 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)

Among string values, the ascending order is lowercase, then uppercase, then accented letters.

Examples

Ex 1: List cities in descending order and then landmarks in ascending order.
Ex 2: List names resulting from a UNION clause.

Example 1: List cities in descending order and then landmarks in ascending order.

SELECT city, name
FROM `travel-sample`
WHERE type = "landmark"
ORDER BY city DESC, name ASC;

Results:

[
  {
    "city": "Évreux",
    "name": "Cafe des Arts"
  },
  {
    "city": "Épinal",
    "name": "Marché Couvert (covered market)"
  },
  {
    "city": "Épinal",
    "name": "Musée de l'Image/Imagerie d'Épinal"
  },
  {
    "city": "Yosemite Valley",
    "name": "Lower Yosemite Fall"
  },
  {
    "city": "Yosemite Valley",
    "name": "Mirror Lake/Meadow"
  },
...

Example 2: List the names of hotels and landmarks resulting from a UNION query.

SELECT name
  FROM `travel-sample`
  WHERE type = "landmark"
UNION SELECT name
  FROM `travel-sample`
  WHERE type = "hotel"
ORDER BY name ASC;

Results:

{
    "name": "'La Mirande Hotel"
  },
  {
    "name": "'The Argyll Arms Hotel"
  },
  {
    "name": "'Visit the Hut of the Shadows and other End of the Road sculptures"
  },
  {
    "name": "02 Shepherd's Bush Empire"
  },
  {
    "name": "101 Coffee Shop"
  },
...