--- title: ORDER BY clause description: The ORDER BY clause sorts the result-set by one or more columns, in ascending or descending order. pubDate: 2026-08-17T09:53:44.266Z antora: editUrl: https://github.com/couchbaselabs/docs-devex/edit/release/8.0/modules/n1ql/pages/n1ql-language-reference/orderby.adoc xref: xref:server:n1ql:n1ql-language-reference/orderby.adoc[] --- [Consult the llms.txt file for a full list of contents](/llms.txt) [View original HTML](/server/current/n1ql/n1ql-language-reference/orderby.html) # ORDER BY clause > The ORDER BY clause sorts the result-set by one or more columns, in ascending or descending order. ## [](#section%5FPurpose)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. ## [](#section%5FPrerequisites)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](../../learn/security/authorization-overview.md). ## [](#section%5FSyntax)Syntax ```ebnf order-by-clause ::= 'ORDER' 'BY' ordering-term ( ',' ordering-term )* ``` ![Syntax diagram](../_images/n1ql-language-reference/order-by-clause.png) ### [](#ordering-term)Ordering Term ```ebnf ordering-term ::= expr ( 'ASC' | 'DESC' )? ( 'NULLS' ( 'FIRST' | 'LAST' ) )? ``` ![Syntax diagram](../_images/n1ql-language-reference/ordering-term.png) ### [](#collation)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`. > [!TIP] > If this query uses an index which includes an index key on the same field as this ordering term, the sort order of the ordering term should match the sort order of the index key to ensure the best performance. > > For example, if the index uses a descending sort order, then the query should also use a descending sort order. If the query uses the opposite sort order to the index, you may experience reduced performance. > > If necessary, you could create two indexes that are identical except for opposite sort orders, and then use an index hint in the query to select the appropriate index. ### [](#nulls-ordering)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)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)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 | > [!NOTE] > * NULL values include JSON NULL. > * String comparison is done using a raw byte collation of UTF8 encoded strings. The ascending order is lowercase, then uppercase, then accented letters. > * Arrays are sorted in order of the first element in each array, then the second element in each array, and so on. In an ascending sort, longer arrays are sorted after shorter arrays, where all the elements are equal as far as the end of the shorter array. > * Objects are first sorted in order of size. In an ascending sort, larger objects are sorted after shorter objects. Where objects are the same size, objects are sorted in order of the first key in each object, then the first value in each object, then the second key in each object, then the second value in each object, and so on. > * SQL++ always sorts the key-value pairs _within each object_ by key in ascending string order before comparison or ordering. ## [](#examples)Examples 1. [List cities in descending order and then landmarks in ascending order](#Ex1) 2. [List the names of hotels and landmarks resulting from a UNION query](#Ex2) Example 1\. List cities in descending order and then landmarks in ascending order ```sqlpp SELECT city, name FROM landmark ORDER BY city DESC, name ASC LIMIT 5; ``` Results: ```json [ { "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 ```sqlpp SELECT name AS sort, type FROM landmark UNION SELECT name AS sort, type FROM hotel ORDER BY sort ASC LIMIT 5; ``` Results: ```json [ { "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.