bastos.sergio bastos.sergio - 1 month ago 6
SQL Question

How to group by looking at previous and next value in each row

Suppose I have a table with the following records...

ResumeId Action
39092 DEV
39092 C
39092 C
39096 C
39096 C
39098 CONF
39098 CONF


How can I group them so that the output would look like this? In the below case, since
ResumeId = 39092
has at least one
DEV
record then the grouping result should return
DEV
...

ResumeId Action
39092 DEV
39096 C
39098 CONF

Answer

Here is one method:

select resumeid,
       (case when max(action) = min(action) then max(action)
             when sum(case when action = 'DEV' then 1 else 0 end) > 0 then 'DEV'
             else '??'
        end)
from t
group by resumeid;

Your rules don't specify what to do if there are multiple actions, but none are DEV.