Skip to content
Developer Edition runs on one host and does not require a product license file. Need multi-host deployment or performance testing at scale? Explore Enterprise Edition

ORDER BY clause

The ORDER BY clause sorts rows by one or more columns either in ascending or descending order.

This clause is part of the Paging statement.

Syntax

order_by_clause
ORDER BY value_expression ASC ASCENDING DESC DESCENDING NULLS FIRST NULLS LAST , 

order_by_clause ::=
    'ORDER BY' (value_expression ( 'ASC' | 'ASCENDING' | 'DESC' | 'DESCENDING' )? ('NULLS FIRST' | 'NULLS LAST')?) ( ',' (value_expression ( 'ASC' | 'ASCENDING' | 'DESC' | 'DESCENDING' )? ('NULLS FIRST' | 'NULLS LAST')?) )*

Parameters

value_expression

The value expression that specifies the column to sort by. Such a column is called a sort key. The supported data types of the sort key include:

For more information about value expressions, see Expressions.

ASC | ASCENDING | DESC | DESCENDING

The sorting rule for the sort key.

  • ASC/ASCENDING: Default rule, which sorts the values in ascending order.
  • DESC/DESCENDING: Sorts the values in descending order.

If there are multiple sort keys, records are first sorted by the first sort key. If the values in the first sort key are identical, records are then sorted by the second sort key, and so on.

For more information about how ordering works for each data type, see Comparison predicate.

NULLS FIRST | NULLS LAST

The order of null values in the sorting results.

  • NULLS FIRST: null values are placed at the beginning of the sorting results. This is the default value when the sorting rule is set to DESC or DESCENDING.
  • NULLS LAST: null values are placed at the end of the sorting results. This is the default value when the sorting rule is set to ASC or ASCENDING.

Examples

Sort in ascending order

'''
TABLE users TYPED TABLE {id INT, name STRING, birthDate DATE} =
    (199, "Julie Kavner", date("1990-01-30")),
    (198, "Tom Cruise", date("1990-04-15")),
    (198, "Amy Hasegawa", date("1990-07-04"))
FOR r IN users
RETURN r.name AS name, r.birthDate AS birthDate
ORDER BY birthDate ASC
'''

The preceding statement explicitly specifies the sorting rule as ASC, which sorts the returned results by birthDate in ascending order.

Return:

+--------------+------------+
| name         | birthDate  |
+--------------+------------+
| Julie Kavner | 1990-01-30 |
| Tom Cruise   | 1990-04-15 |
| Amy Hasegawa | 1990-07-04 |
+--------------+------------+

Sort in descending order

'''
TABLE users TYPED TABLE {id INT, name STRING, birthDate DATE} =
    (199, "Julie Kavner", date("1990-01-30")),
    (198, "Tom Cruise", date("1990-04-15")),
    (198, "Amy Hasegawa", date("1990-07-04"))
FOR r IN users
RETURN r.name AS name, r.birthDate AS birthDate
ORDER BY birthDate DESC
'''

The preceding statement explicitly specifies the sorting rule as DESC, which sorts the returned results by birthDate in descending order.

Return:

+--------------+------------+
| name         | birthDate  |
+--------------+------------+
| Amy Hasegawa | 1990-07-04 |
| Tom Cruise   | 1990-04-15 |
| Julie Kavner | 1990-01-30 |
+--------------+------------+

Sort with null value

'''
TABLE users TYPED TABLE {id INT, name STRING, birthDate DATE} =
    (199, "Julie Kavner", date("1990-01-30")),
    (198, "Tom Cruise", date("1990-04-15")),
    (198, "Amy Hasegawa", null)
FOR r IN users
RETURN r.name AS name, r.birthDate AS birthDate
ORDER BY birthDate DESC NULLS FIRST
'''

The preceding statement sorts the returned results by birthDate in descending order and places null values at the beginning of the results.

Return:

+--------------+------------+
| name         | birthDate  |
+--------------+------------+
| Amy Hasegawa | null       |
| Tom Cruise   | 1990-04-15 |
| Julie Kavner | 1990-01-30 |
+--------------+------------+

Sort with multiple sort keys

You can specify multiple sort keys of different sorting rules. The returned results are first sorted based on the first sort key. If any values are the same for this key, these results are further sorted based on the second sort key. This process continues for any subsequent keys.

'''
TABLE users TYPED TABLE {id INT, name STRING, birthDate DATE} =
    (195, "Julie Kavner", date("1990-01-30")),
    (198, "Tom Cruise", date("1990-04-15")),
    (196, "Amy Hasegawa", date("1990-04-15"))
FOR r IN users
RETURN r.id AS id, r.birthDate AS birthDate, r.name AS name
ORDER BY birthDate ASC, id DESC
'''

The preceding statement specifies birthDate as the first sort key, which sorts in ascending order, and id as the second sort key, which sorts in descending order.

The returned results are first sorted based on birthDate in ascending order. If the values are the same for this key, these results are further sorted based on id in descending order.

Return:

+-----+------------+--------------+
| id  | birthDate  | name         |
+-----+------------+--------------+
| 195 | 1990-01-30 | Julie Kavner |
| 198 | 1990-04-15 | Tom Cruise   |
| 196 | 1990-04-15 | Amy Hasegawa |
+-----+------------+--------------+