Ordering results
Whether you return results as nodes or as properties of nodes, you can specify a property value for the ordering. Strings are ordered by their text values. Boolean true comes before false when ordered. Numeric data (including date and datetime properties) are ordered by their numeric value.
You specify ORDER BY in the RETURN clause where you specify the property for the ordering.
Here is an example:
MATCH (p:Person)
WHERE p.born.year = 1980
RETURN p.name AS name,
p.born AS birthDate
ORDER BY p.bornIn this query, we find all Person nodes with a born year of 1980. The born property of Person nodes is a Cypher Date type, so we can use the year method to test the year value. Notice that is this code we are specifying aliases for the column headers for each row. The rows will be ordered by the value of the born property.
The default ordering is ascending, but you can specify descending as follows:
MATCH (p:Person)
WHERE p.born.year = 1980
RETURN p.name AS name, p.born AS birthDate
ORDER BY p.born DESCEliminating null values returned
For some queries, you may want to not return null values, especially if you are ordering the data.
Here is an example:
MATCH (p:Person)
RETURN p.name AS name, p.born AS birthDate
ORDER BY p.born DESCThis query returns many rows at the beginning of result set that have a null value for the born property. In this case, we don’t want those nodes to be retrieved. We adjust the query as follows:
MATCH (p:Person)
WHERE p.born IS NOT NULL
RETURN p.name AS name, p.born AS birthDate
ORDER BY p.born DESCOrdering multiple results
You can provide multiple sort expressions and the result will be sorted in that order.
MATCH (p:Person)-[:DIRECTED | ACTED_IN]->(m:Movie)
WHERE p.name = 'Tom Hanks'
OR p.name = 'Keanu Reeves'
RETURN m.year, m.title
ORDER BY m.year DESC , m.titleIn this query we return the movies in descending year order and then the movie titles in ascending order.
There is no limit to the number of properties you can order by.
You can order by any property or expression that is within scope of the query. The ordering property or need not be explicitly returned, but can be used for the ordering.
Check your understanding
1. Ordering result rows
We want to return the ratings that Sandy Jones gave movies and return the rating from highest to lowest. What code do you use?
Once you have selected your option, click the Check Results query button to continue.
MATCH (u:User)-[r:RATED]->(m:Movie)
WHERE u.name = 'Sandy Jones'
/*select:RETURN m.title AS movie, r.rating AS rating ORDER BY r.rating DESC*/-
❏
RETURN m.title AS movie, r.rating AS rating ORDER BY r.rating ASC -
✓
RETURN m.title AS movie, r.rating AS rating ORDER BY r.rating DESC -
❏
RETURN m.title AS movie, r.rating ORDERED AS rating DESC -
❏
RETURN m.title AS movie, r.rating ORDERED AS rating
Hint
You are looking to order the results by the rating property in descending order.
Solution
The answer is RETURN m.title AS movie, r.rating AS rating ORDER BY r.rating DESC
2. Ordering results
What is the maximum number of properties can you order in your results?
-
❏ 1
-
❏ 2
-
❏ 8
-
✓ Unlimited
Hint
Come on! Would we really limit the number of properties that you could order by?!
Solution
The answer is Unlimited.
Summary
In this lesson, learned how you can order the rows returned in a query.
In the next challenge, you will write queries to order results.