You can use the ONE ROW PER optional clause in a SQL graph query to determine the number of rows to be returned by the query.

The ONE ROW PER clause can be placed after the MATCH clause (or if present, after a WHERE clause) and before the COLUMNS clause in a SQL graph query.

The ONE ROW PER clause supports the following options which determine the number of rows to be returned by the query:

• ONE ROW PER MATCH (default): This specifies that one row is returned per match to the graph pattern. See Example 5-16 for more information.
• ONE ROW PER VERTEX: This creates one row for each vertex along a path. It declares a single iterator vertex variable and iterates through the vertices in paths, binding the iterator variable to different vertices in different iterations. For each path, it creates as many rows as there are vertices in the path.

  For example, consider the following path with three vertices - (v1) → (v2) → (v3). In this case ONE ROW PER VERTEX (v) creates three rows, one for each of v1 ,v2, and v3.

  For zero hop paths, ONE ROW PER VERTEX (v) creates one row, where v binds to the only vertex (which is the source and destination of the path).

  See Example 5-17 for more information.

• ONE ROW PER STEP: This declares an iterator vertex variable, an iterator edge variable, and another iterator vertex variable. It creates one row for every hop, where the first vertex variable binds to the source of this hop, the edge variable is the edge of the hop and the second vertex variable is the destination of the hop. It iterates through the steps of the different paths. A step is a vertex-edge-vertex triple. If a path is non-empty and thus contains at least one edge and two vertices, then there are as many steps as there are edges and each iteration binds the iterator variables to the next edge and its source and destination in the path.

  For example, consider the following path - v1 -[e1]→ v2 -[e2]→ v3. In this case ONE ROW PER STEP (v1,e,v2) returns the following two rows:

  v1, e1, v2
  v2, e2, v3 

  However, if a path is empty and consists of a single vertex only, then the path has a single step and the first iterator vertex variable binds to that vertex, while the iterator edge variable and the second iterator vertex variable are not bound to any graph element.

  See Example 5-18 for more information.

Note that if ONE ROW PER VERTEX or ONE ROW PER STEP is specified in a SQL graph query, then the MATCH clause must contain a single path pattern only.

The following describes a few restrictions for the iterator variables:

• All iterator variables have to be unique. This implies that these variables need to be different from the graph element variables used in the MATCH clause. In case of ONE ROW PER STEP, all the three iterator variables need to be different.
• Iterator variables cannot be referenced in the graph pattern or graph pattern WHERE clause of the SQL graph query. They can only be used in the COLUMNS clause of the query.

• Using the MATCHNUM Function
  The MATCHNUM function returns a number that uniquely identifies a match in a set of matches.
• Using the ELEMENT_NUMBER Function
  The ELEMENT_NUMBER function can only be used for iterator variables that are defined in the ONE ROW PER VERTEX or ONE ROW PER STEP clause in a SQL graph query.