Clever Geek Handbook

Cypher Neo4j ORDER BY DESC request

I want to order COUNT (Movie.title) in descending order. But this gives an error. This is a request.

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

Thank!

+3


source to share


2 answers


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
+5


source


The error is returned because you cannot order by aggregation directly in Cypher. To order any aggregate, you need to use the WITH clause .

So your query should be (assuming you want to list titles for each actor in each 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 100 limit has now changed its behavior, so you probably want to move it to 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

Also: for your queries to run well, you must have a pointer to the Movie.genre property and you must inject labels for the movie and the person.

+1


source






More articles:


All Articles