Rank over partition by

[jh_lee]

Hi,

I'd like to know how can I use cypher like SQL function "rank over partition by".
For example, I want to get 1st rank data by subject, class.
I'd appreciate advice.

Ex)
Class Subject Score
1 Math 99 (Y)
1 Math 95
1 English 98 (Y)
1 English 97
2 Math 96 (Y)
2 Math 94
2 English 96 (Y)
2 English 93

[giuseppe_villan]

@jh.lee

Assuming you want to do, for example, a rank over "Class" partition by "Subject",
I think you could execute something like that:

MATCH (node:Test)
WHERE node:Test // filter only node with the wanted label
with apoc.coll.sortNodes(collect(node), 'class') as partition, node.subject as partitionName // sort nodes by partition
call {
    with partition
        call {
            with partition
            unwind partition as n
            return collect(n.class) as nodes // create list of class
        }
    unwind partition as n
    set n.rank = apoc.coll.indexOf(nodes, n.class) + 1 // set rank
    return nodes, n
}
return nodes, n // return nodes

where I set in a property rank the result.

This set all existing nodes.
To set new nodes the ideal thing would be to use the Triggers - APOC Documentation , but currently there is a pending bug which prevents correct use Fixes #1152 and #2247: Deleting nodes when having an apoc.trigger registered returns Neo.DatabaseErr...,
so you could use the apoc.periodic.schedule, if you can use the Apoc procedure, to schedule the above query every N seconds.