Update rows in table with id from row before in PostgreSQL

Question

Say I have a table like this:

| id | grp | time_added | previous_id |
|----|-------|------------|-------------|
| 1 | 1 | 5 | null |
| 2 | 1 | 8 | null |
| 3 | 2 | 9 | null |
| 4 | 1 | 12 | null |
| 5 | 2 | 15 | null |

(time_added is actually a date, but numbers are used here for readability)

I want to update the previous_id of each row so that it's equal to the id of the previous row in the same group grp sorted by time_added.

This means that after running the update query, the table above should look like:

| id | grp | time_added | previous_id |
|----|-------|------------|-------------|
| 1 | 1 | 5 | null |
| 2 | 1 | 8 | 1 |
| 3 | 2 | 9 | null |
| 4 | 1 | 12 | 2 |
| 5 | 2 | 15 | 3 |

---

What I have so far is the following query:

UPDATE the_table r
SET previous_id = sub.id
FROM (
 SELECT id, time_added, grp
 FROM the_table
 ORDER BY time_added DESC
) sub
WHERE (
 r.grp = sub.grp
 AND sub.time_added < r.time_added
);

However, I suspect the ORDER BY of the subquery does not do what I want it to, and the previous_id is set to the id of a random row below it, not the one strictly below it.

See this fiddle for an example of what happens. Row 3 is given a previous_id of 1, when it should be 2.

Answer 1

You can get your desired result by using LAG() function.

select
 id,
 grp,
 time_added,
 lag(id) over (partition by grp order by grp, time_added) previous_id
from
 tbl;

You can update in this way:

with x as
(
 select
 id,
 lag(id) over (partition by grp order by grp, time_added) previous_id
 from
 tbl
)
update tbl
set 
 previous_id = x.previous_id
from
 x
where
 x.id = tbl.id;

db<>fiddle here

• Lennart - Slava Ukraini: You can remove grp from order by grp, id
• Hans: Thank you for the tip. What solved my issue was: ``` UPDATE the_table r SET previous_id = sub.p_id FROM ( SELECT id, time_added, grp, lag(id) OVER (PARTITION BY grp ORDER BY time_added) p_id FROM the_table ) sub WHERE sub.id = r.id; ```
• Lennart - Slava Ukraini: @McNets, since ordering is done per grp, I don't see how ordering by grp should matter? Can you give an example where it will make a difference?
• McNets: @Lennart sorry, you're right. In this case order is set by time_added, may be I misread the question.