# Recursive SQL

Published articles for Recursive SQL.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## Advent of Code 2024 in pure SQL

DevFeed: [Advent of Code 2024 in pure SQL](<https://devfeed.tech/articles/advent-of-code-2024-in-pure-sql-25086.md>)

Original publisher: [Read original article](<https://databasearchitects.blogspot.com/2024/12/advent-of-code-2024-in-pure-sql.html>)

Author: Thomas Neumann (noreply@blogger.com)

Published: 2024-12-27T16:57:00Z

Content type: article

Language: en

Sources: [Database Architects](<https://devfeed.tech/sources/database-architects.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Advent of Code](<https://devfeed.tech/topics/advent-of-code.md>), [Algorithms](<https://devfeed.tech/topics/algorithms.md>), [Programming](<https://devfeed.tech/topics/programming.md>), [Graphs](<https://devfeed.tech/topics/graphs.md>)

Tags: [advent-of-code](<https://devfeed.tech/tags/advent-of-code.md>), [algorithm](<https://devfeed.tech/tags/algorithm.md>), [duckdb](<https://devfeed.tech/tags/duckdb.md>), [parsing](<https://devfeed.tech/tags/parsing.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [puzzle](<https://devfeed.tech/tags/puzzle.md>), [recursive-sql](<https://devfeed.tech/tags/recursive-sql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

The author describes solving every Advent of Code 2024 problem in pure SQL. Small-scale traversals were practical and sometimes pleasant, while larger recursive queries could be inefficient and require more than 200 GB of memory. The experience suggests that recursive SQL would benefit from mechanisms for updating state and supporting more complex control flow.

### Source excerpt

On a whim I decided to do this years advent of code in pure SQL. That was an interesting experience that I can recommend to everybody because it forces you to think differently about the problems. And I can report that it was possible to solve every problem in pure SQL. In many cases SQL was actually surprisingly pleasant to use. The full solution for day 11 (including the puzzle input) is shown below: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30with recursive aoc10_input(i) as (select ' 89010123 78121874 87430965 96549874 45678903 32019012 01329801 10456732 '), lines(y,line) as ( select 0, substr(i,1,position(E'\n' in i)-1), substr(i,position(E'\n' in i)+1) from aoc10_input union all select y+1,substr(r,1,position(E'\n' in r)-1), substr(r,position(E'\n' in r)+1) from lines l(y,l,r) where position(E'\n' in r)>0 ), field(x,y,v) as ( select x,y,ascii(substr(line,x::integer,1))-48 from (select * from lines l where line<>'') s, lateral generate_series(1,length(line)) g(x) ), paths(x,y,v,sx,sy) as ( select x,y,9,x,y from field where v = 9 union all select f.x,f.y,f.v,p.sx,p.sy from field f, paths p where f.v=p.v-1 and ((f.x=p.x and abs(f.y-p.y)=1) or (f.y=p.y and abs(f.x-p.x)=1)) and p.v>0), results as (select * from paths where v=0), part1 as (select distinct * from results) select (select count(*) from part1) as part1, (select count(*) from results) as part2 Parsing the input is a bit painful in SQL, but it is not too bad. Lines 1-10 are simply the puzzle input, lines 11-17 split the input into individual lines, and lines 18-21 construct a 2D array from the input. The algorithm itself is pretty short, lines 22-27 perform a recursive traversal of the field, and lines 28-39 extract the puzzle answer from the traversal results. For this kind of small scale traversals SQL works just fine. Other days were more painful. Day 16 for example does conceptually a very similar traversal of a field, and it computes the minimal traversal distance

## How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ

DevFeed: [How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ](<https://devfeed.tech/articles/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq-28945.md>)

Original publisher: [Read original article](<https://blog.jooq.org/how-to-turn-a-list-of-flat-elements-into-a-hierarchy-in-java-sql-or-jooq/>)

Author: lukaseder

Published: 2023-03-24T15:45:28Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [Java](<https://devfeed.tech/topics/java.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Jackson](<https://devfeed.tech/topics/jackson.md>), [JSON](<https://devfeed.tech/topics/json.md>), [Library](<https://devfeed.tech/topics/library.md>)

Tags: [code](<https://devfeed.tech/tags/code.md>), [collections](<https://devfeed.tech/tags/collections.md>), [collector](<https://devfeed.tech/tags/collector.md>), [collectors](<https://devfeed.tech/tags/collectors.md>), [hierarchical-sql](<https://devfeed.tech/tags/hierarchical-sql.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [jackson](<https://devfeed.tech/tags/jackson.md>), [java](<https://devfeed.tech/tags/java.md>), [jooq-development](<https://devfeed.tech/tags/jooq-development.md>), [json](<https://devfeed.tech/tags/json.md>), [nested-collections](<https://devfeed.tech/tags/nested-collections.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [recursive-sql](<https://devfeed.tech/tags/recursive-sql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

### AI overview

A tutorial on converting flat parent-child data into hierarchical structures using SQL, Java, or jOOQ. It compares a recursive PostgreSQL query with jOOQ 3.19 and a reusable pure-Java Collector, and discusses JSON serialization with Jackson and type inference considerations.

### Source excerpt

Occasionally, you want to write a SQL query and fetch a hierarchy of data, whose flat representation may look like this: The result might be: |id |parent_id|label | |---|---------|-------------------| |1 | |C: | |2 |1 |eclipse | |3 |2 |configuration | |4 |2 |dropins | |5 |2 |features | |7 |2 |plugins | |8 |2 ... Continue reading How to Turn a List of Flat Elements into a Hierarchy in Java, SQL, or jOOQ ->