4 ms·
> I suspect that's a pretty non-standard/rarely-used feature though. If you learn SQL you likely won't encounter this Recursive common table expressions are pa
by castorp 3y ago
> I suspect that's a pretty non-standard/rarely-used feature though. If you learn SQL you likely won't encounter this
Recursive common table expressions are part of the SQL standard (since 1999) and are quite frequently used to traverse hierarchical data (aka "adjacency list").
It is part of basically all (good) SQL tutorials - at least in the "advanced" part.
- blowski 3y agoI don't remember using recursion in a real project, but I built a HN clone on top of Postgres, with the following query: WITH RECURSIVE thread(id, parent_id, user_id, post_id, timestamp, text, depth) AS ( SELECT id, parent_id, user_id, post_id, timestamp, text, 0 FROM comments WHERE user_id = 1 AND parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.user_id, c.post_id, c.timestamp, c.text, t.depth + 1 FROM comments c JOIN thread t ON c.parent_id = t.id WHERE c.user_id != t.user_id ) SELECT * FROM thread ORDER BY timestamp ASC;
- epgui 3y agoI wrote a recursive CTE to do a tree traversal on a parent-child “relationship” table a few weeks ago at work. They do come up!