ORDER BY
ORDER BY
specifies how the output of a clause should be sorted.
It be used as a sub-clause following RETURN
or WITH
.
As of Neo4j 5.24, it can also be used as a standalone clause, either on its own or in combination with SKIP
/OFFSET
or LIMIT
.
ORDER BY
relies on comparisons to sort the output, see Ordering and comparison of values.
You can sort on many different values, e.g. node/relationship properties, the node/relationship ids, or on most expressions.
Unless |
Example graph
The following graph is used for the examples below:
To recreate it, run the following query against an empty Neo4j database:
CREATE
(andy: Person {name: 'Andy', age: 34, length: 170}),
(bernard: Person {name: 'Bernard', age: 36}),
(charlotte: Person {name: 'Charlotte', age: 32, length: 185}),
(andy)-[:KNOWS]->(bernard),
(bernard)-[:KNOWS]->(charlotte)
Order nodes by property
ORDER BY
is used to sort the output.
MATCH (n)
RETURN n.name, n.age
ORDER BY n.name
The nodes are returned, sorted by their name.
n.name | n.age |
---|---|
|
|
|
|
|
|
Rows: 3 |
Order nodes by multiple properties
You can order by multiple properties by stating each variable in the ORDER BY
clause.
Cypher® will sort the result by the first variable listed, and for equals values, go to the next property in the ORDER BY
clause, and so on.
MATCH (n)
RETURN n.name, n.age
ORDER BY n.age, n.name
This returns the nodes, sorted first by their age, and then by their name.
n.name | n.age |
---|---|
|
|
|
|
|
|
Rows: 3 |
Order nodes by ID
ORDER BY
is used to sort the output.
MATCH (n)
RETURN n.name, n.age
ORDER BY elementId(n)
The nodes are returned, sorted by their internal ID.
n.name | n.age |
---|---|
|
|
|
|
|
|
Rows: 3 |
Neo4j reuses its internal IDs when nodes and relationships are deleted. Applications relying on internal Neo4j IDs are, as a result, brittle and can be inaccurate. It is recommended to use application-generated IDs instead. |
Order nodes by expression
ORDER BY
is used to sort the output.
MATCH (n)
RETURN n.name, n.age, n.length
ORDER BY keys(n)
The nodes are returned, sorted by their properties.
n.name | n.age | n.length |
---|---|---|
|
|
|
|
|
|
|
|
|
Rows: 3 |
Order nodes in descending order
By adding DESC[ENDING]
after the variable to sort on, the sort will be done in reverse order.
MATCH (n)
RETURN n.name, n.age
ORDER BY n.name DESC
The example returns the nodes, sorted by their name in reverse order.
n.name | n.age |
---|---|
|
|
|
|
|
|
Rows: 3 |
Ordering null
When sorting the result set, null
will always come at the end of the result set for ascending sorting, and first when doing descending sort.
MATCH (n)
RETURN n.length, n.name, n.age
ORDER BY n.length
The nodes are returned sorted by the length property, with a node without that property last.
n.length | n.name | n.age |
---|---|---|
|
|
|
|
|
|
|
|
|
Rows: 3 |
Ordering in a WITH
clause
When ORDER BY
is present on a WITH
clause , the immediately following clause will receive records in the specified order.
The order is not guaranteed to be retained after the following clause, unless that also has an ORDER BY
subclause.
The ordering guarantee can be useful to exploit by operations which depend on the order in which they consume values.
For example, this can be used to control the order of items in the list produced by the collect()
aggregating function.
The MERGE
and SET
clauses also have ordering dependencies which can be controlled this way.
MATCH (n)
WITH n ORDER BY n.age
RETURN collect(n.name) AS names
The list of names built from the collect
aggregating function contains the names in order of the age
property.
names |
---|
|
Rows: 1 |
Ordering aggregated or DISTINCT results
In terms of scope of variables, ORDER BY
follows special rules, depending on if the projecting RETURN
or WITH
clause is either aggregating or DISTINCT
.
If it is an aggregating or DISTINCT
projection, only the variables available in the projection are available.
If the projection does not alter the output cardinality (which aggregation and DISTINCT
do), variables available from before the projecting clause are also available.
When the projection clause shadows already existing variables, only the new variables are available.
It is also not allowed to use aggregating expressions in the ORDER BY
sub-clause if they are not also listed in the projecting clause.
This rule is to make sure that ORDER BY
does not change the results, only the order of them.
ORDER BY and indexes
The performance of Cypher queries using ORDER BY
on node properties can be influenced by the existence and use of an index for finding the nodes.
If the index can provide the nodes in the order requested in the query, Cypher can avoid the use of an expensive Sort
operation.
Read more about this capability in Range index-backed ORDER BY.
Using ORDER BY
as a standalone clause
ORDER BY
can be used as a standalone clause, or in conjunction with SKIP
/OFFSET
or LIMIT
.
ORDER BY
MATCH (n)
ORDER BY n.name
RETURN collect(n.name) AS names
names |
---|
|
|
The following query orders all nodes by name
descending, skips the first row and limits the results to one row.
ORDER BY
used in conjunction with SKIP
and LIMIT
MATCH (n)
ORDER BY n.name DESC
SKIP 1
LIMIT 1
RETURN n.name AS name
name |
---|
|
|