0

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     |
 |-------------------|------------|
user3580480
  • 442
  • 7
  • 14
  • 45
  • https://stackoverflow.com/questions/18987791/how-do-i-efficiently-select-the-previous-non-null-value – Vao Tsun Jan 31 '18 at 14:31
  • 1
    Thanks Vao, that solution worked. I have added by code below. Unless anyone else is trying to achieve the same with joined tables – user3580480 Jan 31 '18 at 15:55

1 Answers1

0

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
user3580480
  • 442
  • 7
  • 14
  • 45