Understanding ORDER BY in Cypher: Sorting Results in Apache ageDB

Introduction

In Apache ageDB, the ORDER BY clause plays a crucial role in sorting query results based on specified criteria. However, it's important to note that ORDER BY operates exclusively on properties of nodes and relationships rather than the nodes or relationships themselves. This blog post will delve into the intricacies of ORDER BY in Apache ageDB and provide examples of its usage.

Scope of Variables in ORDER BY

The scope of variables in the ORDER BY clause depends on whether the preceding RETURN or WITH clause is aggregating or DISTINCT. In the case of an aggregating or DISTINCT projection, only variables available within the projection can be utilized. However, if the projection does not change the output cardinality, variables from before the projecting clause are also accessible. When shadowing existing variables, only the new variables become available.

Furthermore, it's important to note that using aggregating expressions in the ORDER BY sub-clause is only allowed if they are also listed in the projecting clause. This restriction ensures that ORDER BY solely alters the order of the results and does not affect the outcome itself.

Ordering Nodes by a Single Property

To sort nodes by a specific property, the ORDER BY clause is employed. Consider the following query:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name AS name, n.age AS age
    ORDER BY n.name
    RETURN name, age
$$) AS (name agtype, age agtype);

In this example, nodes are returned sorted in ascending order based on their "name" property.

Result

name    age
"A" 34
"B" 34
"C" 32
(1 row)

Ordering Nodes by Multiple Properties

Apache ageDB allows sorting by multiple properties. By listing each variable in the ORDER BY clause, the result set can be sorted based on a priority order. If values in the first property are equal, the sorting continues based on the subsequent properties listed. Consider the following query:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name AS name, n.age AS age
    ORDER BY n.age, n.name
    RETURN name, age
$$) AS (name agtype, age agtype);

This query sorts nodes first by their "age" property and then by their "name" property.

Result

name    age
"C" 32
"A" 34
"B" 34
(1 row)

Ordering Nodes in Descending Order:

To sort nodes in descending order, the DESC or DESCENDING keyword is appended to the ORDER BY clause. Let's consider the following example:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name AS name, n.age AS age
    ORDER BY n.name DESC
    RETURN name, age
$$) AS (name agtype, age agtype);

In this case, nodes are sorted in reverse order based on their "name" property.

Result

name    age
"C" 32
"B" 34
"A" 34
(3 rows)

Handling Null Values in Ordering

When sorting result sets, null values are treated differently depending on the sorting order. For ascending sorting, null values appear at the end of the result set, whereas they appear first in descending sorting. Consider the following query:

SELECT *
FROM cypher('graph_name', $$
    MATCH (n)
    WITH n.name AS name, n.age AS age, n.height
    ORDER BY n.height
    RETURN name, age, height
$$) AS (name agtype, age agtype, height agtype);

In this example, nodes are sorted based on their "height" property, with nodes lacking that property appearing last in the ascending order.

Results

name    age height
"A" 34  170
"C" 32  185
"B" 34  <NULL>
(3 rows)

Conclusion

Understanding how to sort query results using the ORDER BY clause is essential in Apache ageDB. By leveraging ORDER BY, you can arrange results based on specific