ORDER BY#

GQL clause

Sorts the working rows. It is a clause of its own, so it can sort anywhere in a pipeline, not only at the end.

Synopsis#

ORDER BY sort_key [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]

bold keywords · italic placeholders you supply · [ ] optional · ... repeatable. See Syntax conventions.

Description#

ORDER BY sorts the rows currently in the pipeline. It is written after the clause whose rows it sorts, and it applies to that clause’s output. That clause is a RETURN, a WITH or a MATCH.

Rows are compared by the first sort key; ties go to the next, and so on.
Each key sorts ascending unless you write DESC. ASC is the explicit default.
Nulls sort last under ASC and first under DESC, unless NULLS FIRST or NULLS LAST says otherwise.
A sort key is an expression, not a column number. ORDER BY 2 sorts by the constant 2, which orders nothing. See Compatibility.
ORDER BY cannot lead a query and cannot end one.
After RETURN DISTINCT, every sort key has to appear in the projection.

It is a standalone clause, so it can also sort in the middle of a pipeline. A LIMIT usually follows, to cut the rows a later clause has to process.

Parameters#

sort_key: An expression over the bindings in scope. After a RETURN or WITH this includes that clause’s aliases; it may also be an expression that was not projected at all.
ASC, DESC: Ascending or descending. ASC is the default.
NULLS FIRST, NULLS LAST: Where null values go. The default is NULLS LAST for an ascending key and NULLS FIRST for a descending one.

Examples#

Every example runs against the example dataset.

Sort by a projected column#

MATCH (m:movie) WHERE m.year > 2015
RETURN m.title AS film, m.year AS year
ORDER BY year;

            film            | year
----------------------------+------
 "Blade Runner 2049"        | 2017
 "The Matrix Resurrections" | 2021
(2 rows)

Sort by something you did not project#

The sort key is an ordinary expression, so it need not be a result column:

MATCH (m:movie)
RETURN m.title AS film
ORDER BY m.year
LIMIT 3;

       film
------------------
 "Blade Runner"
 "The Terminator"
 "Total Recall"
(3 rows)

MATCH (p:person)
RETURN p.name AS person
ORDER BY length(p.name) DESC
LIMIT 3;

         person
-------------------------
 "Arnold Schwarzenegger"
 "Matthew McConaughey"
 "Laurence Fishburne"
(3 rows)

Sort by several keys, in different directions#

MATCH (m:movie)
RETURN m.genre AS genre, m.year AS year, m.title AS film
ORDER BY genre, year DESC
LIMIT 5;

  genre   | year |            film
----------+------+----------------------------
 "Action" | 2021 | "The Matrix Resurrections"
 "Action" | 2012 | "The Avengers"
 "Action" | 2003 | "The Matrix Reloaded"
 "Action" | 2003 | "The Matrix Revolutions"
 "Action" | 1999 | "The Matrix"
(5 rows)

genre ascending, year descending within each genre.

Place the nulls#

An OPTIONAL MATCH leaves nulls behind, which makes the default visible. Ascending puts them last:

MATCH (p:person) WHERE p.born > 1974
OPTIONAL MATCH (p)-[:directed]->(m:movie)
RETURN p.name AS person, m.title AS directed
ORDER BY directed;

      person       | directed
-------------------+-----------
 "Neill Blomkamp"  | "Elysium"
 "Ryan Gosling"    |
 "Sam Worthington" |
(3 rows)

Descending puts them first:

MATCH (p:person) WHERE p.born > 1974
OPTIONAL MATCH (p)-[:directed]->(m:movie)
RETURN p.name AS person, m.title AS directed
ORDER BY directed DESC;

      person       | directed
-------------------+-----------
 "Ryan Gosling"    |
 "Sam Worthington" |
 "Neill Blomkamp"  | "Elysium"
(3 rows)

NULLS FIRST asks for that placement without reversing the sort:

MATCH (p:person) WHERE p.born > 1974
OPTIONAL MATCH (p)-[:directed]->(m:movie)
RETURN p.name AS person, m.title AS directed
ORDER BY directed NULLS FIRST;

      person       | directed
-------------------+-----------
 "Ryan Gosling"    |
 "Sam Worthington" |
 "Neill Blomkamp"  | "Elysium"
(3 rows)

Sort in the middle of a pipeline#

ORDER BY after a MATCH, with a LIMIT, narrows what the rest of the query sees:

MATCH (m:movie)
ORDER BY m.year DESC LIMIT 2
MATCH (p:person)-[:directed]->(m)
RETURN m.title AS film, p.name AS director
ORDER BY film;

            film            |      director
----------------------------+--------------------
 "Blade Runner 2049"        | "Denis Villeneuve"
 "The Matrix Resurrections" | "Lana Wachowski"
(2 rows)

The second MATCH runs against two rows, not eighteen.

Where ORDER BY cannot go#

It cannot open a query:

ORDER BY 1;

ERROR:  syntax error at or near "ORDER"
LINE 1: ORDER BY 1;
        ^

And it cannot close one, because a sort produces no result on its own:

MATCH (m:movie) ORDER BY m.title;

ERROR:  Cypher query must end with RETURN, FINISH or update clause

Notes#

Sorting a property sorts a jsonb value. jsonb orders by type before value: a number sorts above every string, and both above null. So a key whose values have mixed types groups by type first. Cast the key when the stored types are mixed and you want one ordering across them.

Compatibility#

A number is a sort key, not a column reference#

In SQL, ORDER BY 2 sorts by the second output column. Here the sort key is a value expression, so the integer is a constant: every row compares equal, nothing is reordered, and no error is raised.

MATCH (m:movie) WHERE m.year > 2015
RETURN m.title AS film, m.year AS year
ORDER BY 2;

            film            | year
----------------------------+------
 "The Matrix Resurrections" | 2021
 "Blade Runner 2049"        | 2017
(2 rows)

The rows come back in scan order, not by year. Naming the key sorts correctly, as the first example on this page shows. This follows ISO/IEC 39075 §16.17, whose <sort key> is an <aggregating value expression> with no ordinal form; the deviation is from SQL. Always name the sort key.

ASCENDING and DESCENDING (ISO/IEC 39075 §16.17)#

The standard accepts ASCENDING and DESCENDING alongside ASC and DESC. AgensGraph accepts only the short spellings; ORDER BY film ASCENDING is a syntax error.