postgresql - Populate zero values in column with next value greater than zero

Question

I have the following Postres code:

SELECT
a.assessmentid,
b.groupid
FROM wo_assessment a
LEFT JOIN wo_group_info b ON a.assessmentid = b.assessmentid
WHERE a.workorderid=2
ORDER BY a.assessmentid

Which returns the following results:

 |-------------------|------------|
 |    assessmentid   |   groupid  |
 |-------------------|------------|                 
 |         5         |      5     |  
 |-------------------|------------|                 
 |         6         |      4     |
 |-------------------|------------|
 |         7         |      0     |
 |-------------------|------------|                 
 |         8         |      5     |
 |-------------------|------------|
 |         9         |      0     |
 |-------------------|------------|                 
 |        10         |      0     |
 |-------------------|------------|

I would like to populate the 0 values in the groupid field with the next number above in that column, that isn't 0.

So for example I want my table to look like this:

 |-------------------|------------|
 |    assessmentid   |   groupid  |
 |-------------------|------------|                 
 |         5         |      5     |  
 |-------------------|------------|                 
 |         6         |      4     |
 |-------------------|------------|
 |         7         |      4     |
 |-------------------|------------|                 
 |         8         |      5     |
 |-------------------|------------|
 |         9         |      5     |
 |-------------------|------------|                 
 |        10         |      5     |
 |-------------------|------------|

Answer 1

Here is what worked for me:

SELECT q.assessmentid, 
first_value(b.groupid ) over (partition by value_partition order by q.assessmentid) FROM (
 SELECT a.assessmentid,
    b.groupid ,
    sum(case when b.groupid is null then 0 else 1 end) over (order by a.assessmentid) as value_partition
FROM wo_assessment as a
LEFT JOIN wo_group_info b ON a.assessmentid = b.assessmentid
ORDER BY  a.assessmentid ) as q
LEFT JOIN wo_group_info b ON q.assessmentid = b.assessmentid