## WITH RECURSIVE query to choose the longest paths

I am new to `WITH RECURSIVE` in PostgreSQL. I have a reasonably standard recursive query that is following an adjacency list. If I have, for example:

```1 -> 2
2 -> 3
3 -> 4
3 -> 5
5 -> 6
```

it produces:

```1
1,2
1,2,3
1,2,3,4
1,2,3,5
1,2,3,5,6
```

What I would like is to have just:

```1,2,3,4
1,2,3,5,6
```

But I can't see how to do this in Postgres. This would seem to be "choose the longest paths" or "choose the paths that are not contained in another path". I can probably see how to do this with a join on itself, but that seems quite inefficient.

An example query is:

```WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
SELECT g.id, g.link, g.data, 1, ARRAY[g.id], false
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path)
FROM graph g, search_graph sg
WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;
```

Find Longest Path Between Two Nodes using SQL Server , Find Longest Path Between Two Nodes using SQL Server Recursive CTE Query Then continue by selecting second path intervals starting from these new  It can be proved using contradiction. So our algorithm reduces to simple two BFSs. First BFS to find an end point of the longest path and second BFS from this end point to find the actual longest path. For the proof of why does this algorithm works, there is nice explanation here Proof of correctness: Algorithm for diameter of a tree in graph

Recursive Graph Queries, longest, string, Query for the longest matching path, - recursive as A simple non​-recursive graph query can easily map chosen relations of a whole collection. The query was what's known in database-land as a Recursive Common Table Expression (Recursive CTE) --- a rarely-used device, but one of some deep computational power and significance, as it happens to be the magic ingredient that makes SQL Turing Complete. To illustrate what the Recursive CTE is capable of, we'll make some brief forays into

I'm not sure that this should be considered as ugly join solution.

```WITH recursive graph (child, parent) AS (
SELECT 2, 1
UNION
SELECT 3, 2
UNION
SELECT 4, 2
UNION
SELECT 6, 5
UNION
SELECT 7, 6
UNION
SELECT 6, 7
),
paths (start, node, depth, path, has_cycle, terminated) AS (
SELECT
ARRAY[g1.parent],
false,
false
FROM graph g1
WHERE true
AND NOT EXISTS (SELECT 1 FROM graph g2 WHERE g1.parent = g2.child)
UNION ALL
SELECT
p.path || g.child,
g.child = ANY(p.path),
g.parent is null AS terminated
FROM paths p
LEFT OUTER JOIN graph g ON g.parent = p.node
WHERE NOT has_cycle
)
SELECT * from path WHERE terminated
;
```

So the trick is to use `terminated` column via using `LEFT OUTER JOIN` and then select only terminated paths.

Recursion Exercises, As a guide to test the accuracy of your SQL queries, the correct query results over the lengths of the shortest and longest paths between each pair of nodes. To practice writing hierarchical queries like this one, I recommend our interactive course Recursive Queries. Oracle – hierarchical queries In Oracle you can use either the hierarchical query clause (also known as “CONNECT BY query”) or recursive subquery factoring (introduced in version 11g release 2).

PostGIS recursive query in order to find the longest connected , WITH RECURSIVE connected_line AS ( SELECT 0 AS it, ARRAY[a.id] AS path, a​.geom AS geom FROM network a WHERE id = 1 UNION ALL  Execute recursive term with Ri as an input to return the result set Ri+1 as the output. Repeat step 2 until an empty set is returned. (termination check) Return the final result set that is a UNION or UNION ALL of the result set R0, R1, … Rn; PostgreSQL recursive query example. We will create a new table to demonstrate the PostgreSQL

Specifying Big Data Benchmarks: First Workshop, WBDB 2012, San , By our convention not having a from clause implies that the query is over the entire However, due to the construction process of the heterogeneous graphs, query will never use hierarchical graph components. A variant of this query will ask for select disjoint path(p). Find the longest path containing a single edge label. Recursive queries are used to query hierarchical data. The SQL standard defines a special syntax for common table expressions to enable recursive processing. Assume the following hierarchical definition of product categories:

Rules in Database Systems: Second International Workshop, RIDS , It also pays attention to the fact that linear recursive queries have to be of the longest path, the number of transitive closures and data skew for choosing the  Showing the path from root to current row (sys_connect_by_path) Again, start by selecting the value you want in the base query. In the recursive part, append the values you want to add with an appropriate separator. Identifying the leaves (connect_by_isleaf) Displaying leaf rows is more complex with recursive with.

• BTW: your intended output `1,2,3,4 | 1,2,3,5,6` cannot exist, since each node only has one `link` field, and thus only one successor. ('3' has both '4' and '5' as successors.