1

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.

1 Answer 1

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

4
  • You can remove grp from order by grp, id Jan 16, 2019 at 16:22
  • 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; ```
    – Hans
    Jan 16, 2019 at 16:28
  • @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? Jan 16, 2019 at 21:40
  • @Lennart sorry, you're right. In this case order is set by time_added, may be I misread the question.
    – McNets
    Jan 16, 2019 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.