ORDER BY clause
- reference
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
order-by-clause ::= 'ORDER' 'BY' ordering-term ( ',' ordering-term )*
Collation
The collation clause determines the order of the search.
ASC
-
The results are ordered in ascending order.
DESC
-
The results are ordered in descending order.
If the collation clause is missing, the default is ASC
.
Nulls Ordering
The nulls ordering clause determines how NULL or MISSING values are treated when ordering the results:
NULLS FIRST
-
If any results evaluate to NULL or MISSING, those results ordered first.
NULLS LAST
-
If any results evaluate to NULL or MISSING, those results are ordered last.
If the nulls ordering clause is omitted, the default is NULLS FIRST
for an ascending search with ASC
, or NULLS LAST
for a descending search with DESC
.
Arguments
- expr
-
[Required] The identifier or expression by which to order the query results. This may be a document field, a new expression, or an alias in the SELECT clause.
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 first to last:
ASC NULLS FIRST | ASC NULLS LAST | DESC NULLS FIRST | DESC NULLS LAST |
---|---|---|---|
MISSING |
FALSE |
NULL |
BINARY |
NULL |
TRUE |
MISSING |
OBJECT |
FALSE |
NUMBER |
BINARY |
ARRAY |
TRUE |
STRING |
OBJECT |
STRING |
NUMBER |
ARRAY |
ARRAY |
NUMBER |
STRING |
OBJECT |
STRING |
TRUE |
ARRAY |
BINARY |
NUMBER |
FALSE |
OBJECT |
MISSING |
TRUE |
NULL |
BINARY |
NULL |
FALSE |
MISSING |
|
Examples
SELECT city, name
FROM `travel-sample`.inventory.landmark
ORDER BY city DESC, name ASC
LIMIT 5;
[
{
"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"
}
]
SELECT name AS sort, type
FROM `travel-sample`.inventory.landmark
UNION SELECT name AS sort, type
FROM `travel-sample`.inventory.hotel
ORDER BY sort ASC
LIMIT 5;
[
{
"sort": ""Hippie Temptation" house",
"type": "landmark"
},
{
"sort": "'La Mirande Hotel",
"type": "hotel"
},
{
"sort": "'The Argyll Arms Hotel",
"type": "landmark"
},
{
"sort": "'Visit the Hut of the Shadows and other End of the Road sculptures",
"type": "landmark"
},
{
"sort": "02 Shepherd's Bush Empire",
"type": "landmark"
}
]
Note that the name
field in the first SELECT statement and the name
field in the second SELECT statement give two different result expressions, so you cannot use the name
field to order all the results of the UNION query together.
To do this, you must give the name
fields in the two SELECT statements an identical alias, and order the results by that alias.