3

I got a lot of nodes, some with similar values in field X, I want to select by distinct X values and take all the popular nodes (order by some other field Y) with all their properties.

Example:

ID | X | Y | Name

1 | A | 100 | David

2 | A | 10 | Chris

3 | B | 5 | Brad

4 | B | 25 | Amber

Should return:

1 | A | 100 | David

4 | B | 25 | Amber

I managed to get the list by distinct X:

MATCH (u:NodeType)
RETURN DISTINCT u.X

I need to find the most popular (highest value of Y) nodes to join with my distinct nodes (which are now only a single property) and return whole nodes (with all the properties).

Gabor Szarnyas
  • 4,410
  • 3
  • 18
  • 42
Eking
  • 758
  • 8
  • 16

1 Answers1

2

You are looking for an arg max-style query. I recently answered a similar problem using collect:

MATCH (u:NodeType)
WITH u
ORDER BY u.Y DESC
WITH u.X AS X, collect(u)[0] AS u
RETURN u

The idea is the following:

  1. Order by the value of Y (descending).
  2. Implicitly group by the values of X and for the aggregating function, use collect to gather other values to a list. The elements of the list are the nodes (which are still stored according to a descending order of Y).
  3. For each collected list, select the first element with [0].

Maybe the query is a bit easier to read if you perform the last step in a separate clause (and not in the WITH clause that performs the collect):

MATCH (u:NodeType)
WITH u
ORDER BY u.Y DESC
WITH u.X AS X, collect(u) AS us
RETURN us[0] AS u
Gabor Szarnyas
  • 4,410
  • 3
  • 18
  • 42
  • 1
    Good answer, but the Cypher syntax is not quite correct. For instance, the first query should be something like this: `MATCH (u:NodeType) WITH u ORDER BY u.Y DESC WITH u.X AS x, COLLECT(u)[0] AS u RETURN u;` – cybersam Dec 10 '17 at 02:39
  • Good point, thanks! I edited the post quite extensively and this slipped through. – Gabor Szarnyas Dec 10 '17 at 04:30