Cypher - get the latest node

Question

Here's my model: (:A)<--(:B)<-[:R {timestamp}]-(:C {number})

What I'm trying to achieve in one cypher query is to get the latest C nodes, according to the latest R.timestamp (or C.number) for a given A and all the B nodes. That is returning one A, multiple Bs and one C related to each B with the highest R.timestamp. The best would be to also return B nodes without any relation to C.

In SQL I would group by and rank to return only the n rows that I wish per group, I don't have enough experience with COLLECT or UNWIND to achieve the same result.

Answer 1

First find your a node and then optionally match on the b and c nodes. This will also find b nodes that don't have a relationship to a.

MATCH (a:A)
WHERE a.name = {name}
OPTIONAL MATCH (c:C)-[:R]->(b:B)-[:R]->(a)
WITH a, b, c
ORDER BY c.number
WITH a, b, last(collect(c)) AS most_recent_c
RETURN a, b, most_recent_c;