cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! Site migration is underway. Phase 2: migrate recent content

How to group by and get the latest record with column combination

Kailash
Graph Buddy

i have a node with few columns and below are few of the results in it

Email1 Email2 ContactDateInStr
abc@test.com xyz@test.com 20190604
abc@test.com xyz@test.com 20190512
suv@test.com abc@test.com 20190604
def@test.com xyz@test.com 20190515

The result should be below where i am considering combination of Email1 and Email2 and taking only latest date(string) .

Email1 Email2 ContactDateInStr
abc@test.com xyz@test.com 20190604
suv@test.com abc@test.com 20190604
def@test.com xyz@test.com 20190515

any suggestion?

1 ACCEPTED SOLUTION

Yes, after the match, after you've projected out variables, just use the max() aggregation.

...
WITH email1, email2, max(contactDateInStr) as latestDate
...

Aggregations are with respect to the non-aggregation variables which become the grouping key, so this is the max contact date string per each distinct email1, email2 combination.

View solution in original post

3 REPLIES 3

Yes, after the match, after you've projected out variables, just use the max() aggregation.

...
WITH email1, email2, max(contactDateInStr) as latestDate
...

Aggregations are with respect to the non-aggregation variables which become the grouping key, so this is the max contact date string per each distinct email1, email2 combination.

Yes , this would work if i just need the combination and max of date. Thanks!

blackp323
Node

hello everyone, I have a final result see below.. here i have UNION matched the 2 queries. but i required only the last result based on the nodetype. how to do this ??

label value nodetype
"App-PolicyManagement" "App-PolicyManagement" "Application"
"App-CustomerManagement" "App-CustomerManagement" "Application"
"SSMAP" "SSMAP" "Screen"