Cypher Neo4j ORDER BY DESC query

Question

I want to order the COUNT(Movie.title) in descending order. But it gives an error. This is the query.

MATCH (Movie {genre:"Action"})<-[:ACTS_IN]-(Person)
                 "RETURN Person.name, Movie.genre,  COUNT(Movie.title)"
                 "ORDER BY COUNT(Movie.title) DESC"
                 "LIMIT 100";

Thanks!

Solution 1

You can use this query:

MATCH (movie:Movie {genre:"Action"})<-[:ACTS_IN]-(person:Person)
RETURN person.name, movie.genre,  COUNT(distinct movie.title) AS cnt
ORDER BY cnt DESC
LIMIT 100

Solution 2

The error is returned because you cannot order by an aggregate immediately in Cypher. To order by any aggregate you need to use the WITH operator.

So your query should be (assumes that you want to list the titles per actor per genre):

MATCH (Movie {genre:"Action"})<-[:ACTS_IN]-(Person)
RETURN Person.name, Movie.genre,  COUNT(Movie.title)
WITH Person.name AS name, Movie.genre AS genre, COLLECT(Movie.title) AS titles
RETURN name, genre, titles
ORDER BY LENGTH(titles) DESC
LIMIT 100

The limit 100 has now changed its behaviour so you probably want to move it up into the query:

MATCH (Movie {genre:"Action"})<-[:ACTS_IN]-(Person)
RETURN Person.name, Movie.genre,  COUNT(Movie.title)
WITH Person, Movie
LIMIT 100
WITH Person.name AS name, Movie.genre AS genre, COLLECT(Movie.title) AS titles
RETURN name, genre, titles
ORDER BY LENGTH(titles) DESC

Aside: to make your queries perform well you should have an Index on the Movie.genre property and you should introduce labels for Movie and Person.