How to get the max value of a property by grouping on another property in neo4j

Rach Doizl 0
Viewed 26

I am trying to figure out how to do the following in neo4j. I have a result list that looks like this

ID Group Name Status
1 Alpha Active
2 Bravo Active
3 Alpha Active
4 Charlie Active
5 Bravo Active
6 Bravo Active
7 Charlie Active
8 Alpha Active
9 Charlie Active
10 Alpha Active
11 Bravo Active
12 Alpha Active

I want to first group these by group name to conceptually get this.

ID Group Name Status
1 Alpha Active
3 Alpha Active
8 Alpha Active
10 Alpha Active
12 Alpha Active
ID Group Name Status
2 Bravo Active
5 Bravo Active
6 Bravo Active
11 Bravo Active
ID Group Name Status
4 Charlie Active
7 Charlie Active
9 Charlie Active

So that I can then get the max ID from each distinct group and then set the status of all other nodes to inactive except for the node with the max ID of each group so the final result would look like this

ID Group Name Status
1 Alpha Inactive
3 Alpha Inactive
8 Alpha Inactive
10 Alpha Inactive
12 Alpha Active
ID Group Name Status
2 Bravo Inactive
5 Bravo Inactive
6 Bravo Inactive
11 Bravo Active
ID Group Name Status
4 Charlie Inactive
7 Charlie Inactive
9 Charlie Active

My understanding so far has been to do the following in order to get distinct groups to work on

match (g: Groups)
WITH g, collect(distinct g.groupName) as names

But beyond this I am unsure how to get max status on the group to work. I am not sure if I should alternatively be taking a different approach in neo4j in order to get the result I want and set those node properties for each group.

cypher neo4j
1 Answers

I will do the reverse instead. Set all status into Inactive then update the status = Active for those groupName with max id.

// do a mass update of all groups as inactive
match (n: Groups) set n.status = 'Inactive'
// find the id per group name
with   n.groupName as name, max(n.id) as mx_id
// update the status back to Active
match (g: Groups {groupName: name, id: mx_id}) set g.status = 'Active'
return g
jose_bacoy0
Related