Ordering Returned Results

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:

cypher
MATCH (p:Person)
WHERE p.born.year = 1980
RETURN p.name AS name,
p.born AS birthDate
ORDER BY p.born

In 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:

cypher
MATCH (p:Person)
WHERE p.born.year = 1980
RETURN p.name AS name, p.born AS birthDate
ORDER BY p.born DESC

Eliminating 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:

cypher
MATCH (p:Person)
RETURN p.name AS name, p.born AS birthDate
ORDER BY p.born DESC

This 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:

cypher
MATCH (p:Person)
WHERE p.born IS NOT NULL
RETURN p.name AS name, p.born AS birthDate
ORDER BY p.born DESC

Ordering multiple results

You can provide multiple sort expressions and the result will be sorted in that order.

cypher
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.title

In 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.

cypher
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.