CODEWITHSUNDEEP
neo4jcypher
JeffA
JeffA

Reputation: 166

Neo4j Cypher sort on collections

I have a query (below) that returns collections of nodes containing create dates. I want to sort the rows by newest create date. Some of the rows have one create date, other rows have several. As can be seen, the node "name4" should be first. What corrections should I make to my query? Thanks.

MATCH (node:node) 
WITH node 
ORDER BY node.created DESC 
RETURN count(node.name) AS count, node.name, collect(node.created) AS created

count   node.name   created
3       "name1"     [1410234609620,1410234606534,1410234506799]
1       "name2"     [1410234434936]
1       "name3"     [1410234454573]
2       "name4"     [1410463902552,1410460561481]
1       "name5"     [1410234485185]
2       "name6"     [1410234548527,1410234525740]

Upvotes: 0

Views: 6799

Answers (2)

cybersam
cybersam

Reputation: 67044

This query first finds the max timestamp, x, for each name, and then sorts the results.

MATCH (node:node)
WITH node.name AS name, COLLECT(node.created) AS created
WITH name, created, REDUCE(t = 0, c IN created | CASE WHEN c > t THEN c ELSE t END) AS x
RETURN LENGTH(created) AS count, name, created
ORDER BY x DESC

Upvotes: 2

Kenny Bastani
Kenny Bastani

Reputation: 3308

Assuming your timestamps are correctly ordered within your collections, you can take the first timestamp from each of the collections using HEAD([1, 2, 3]), which will return the first element of the collection. I've loaded this value into the timeSort property and used it as the ordering criteria.

MATCH (node:node) 
WITH node
ORDER BY node.created DESC 
WITH count(node.name) AS count, node.name as name, collect(node.created) AS created
WITH count, name, head(created) as timeSort, created
RETURN count, name, created
ORDER BY timeSort DESC

Upvotes: 3

Related Questions