# Calculate a column value backwards over a series of previous rows/RECURSIVE/CONNECTED BY

need your help. I guess/hope there is a function for that. I found “CONNECT DBY” and “WITH RECURSIVE AS …” but it doesn’t seem to solve my problem.

GIVEN TABLES:

Table A

```+------+------------+----------+
| id   |   prev_id  |  date    |
+------------------------------+
| 1    |            | 20200101 |
| 23   |     1      | 20200104 |
| 34   |     23     | 20200112 |
| 41   |     34     | 20200130 |
+------------------------------+
```

Table B

```+------+-----------+
| ref_id  |  key   |
+------------------+
|    41   |  abc   |
+------------------+
```

(points always to the lates entry in table “A”. Update, no history)

Join Statement:

SELECT id, prev_id, key, date FROM A LEFT OUTER JOIN B ON B.ref_id = A.id

GIVEN psql result set:

```+------+------------+----------+-----------+
| id   |   prev_id  |    key   |   date    |
+------------------------------+-----------+
| 1    |            |          | 20200101  |
| 23   |     1      |          | 20200104  |
| 34   |     23     |          | 20200112  |
| 41   |     34     |   abc    | 20200130  |
+------------------------------+-----------+
```

DESIRED output:

```+------+------------+----------+-----------+
| id   |   prev_id  |    key   |   date    |
+------------------------------+-----------+
| 1    |            |   abc    | 20200101  |
| 23   |     1      |   abc    | 20200104  |
| 34   |     23     |   abc    | 20200112  |
| 41   |     34     |   abc    | 20200130  |
+------------------------------+-----------+
```

The rows of the result set are connected by columns ‘id’ and ‘prev_id’. I want to calculate the “key” column in a reasonable time. Keep in mind, this is a very simplified example. Normally there are a lot of more rows and different keys and id’s

I understand that you want to bring the hierarchy of each row in `tableb`. Here is one approach using a recursive query:

```with recursive cte as (
select a.id, a.prev_id, a.date, b.key
from tablea a
inner join tableb b on b.ref_id = a.id
union all
select a.id, a.prev_id, a.date, c.key
from cte c
inner join tablea a on a.id = c.prev_id
)
select * from cte
```
User contributions licensed under: CC BY-SA
5 People found this is helpful