SQL - Loop through rows twice and retroactively change values

Question

I'm writing a code where I want to query the database to update instances of a log to reflect the final status of the project. For example:

this table

would be translated to:

this table

My code so far is:

SELECT a.ID, a.date, a.eval_group,
CASE WHEN a.date > b.date 
AND eval_group = ('completed' OR 'canceled')
THEN 
b.eval_group
ELSE
a.eval group
END AS new_eval_group

FROM temp as a
JOIN temp as b
ON a.ID = b.ID

I'm not sure how to proceed from here and would appreciate any direction. I'm not used to SQL and do not know how to make this function work within the language!

You probably want AND eval_group IN ('completed', 'canceled') in the case expression. — jarlh
– jarlh, Commented Mar 4, 2019 at 14:24

Gordon Linoff · Accepted Answer · 2019-03-04 15:12:30Z

1

You can use window functions for this. You seem to want the last status, which you can get using first_value() and a descending sort:

select t.*,
       first_value(eval_group) over (partition by id order by date desc) as new_eval_group
from t;

If you only want "finished" and "canceled", you can use filter:

select t.*,
       first_value(eval_group) filter (where eval_group in ('finished', 'canceled') over (partition by id order by date desc) as new_eval_group
from t;

answered Mar 4, 2019 at 15:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Loop through rows twice and retroactively change values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related