How do I give every value within a given group the same ID number, incrementing up by one with every new group? In the below example, I want to group together all the rows where the first row is "dog." The first instance of "dog" is assigned ID=1. If the next row down is not also "dog," then set its ID=1. Keep going until you hit another "dog" row. Then increment up by 1. And so forth.
Value | ID |
---|---|
dog | 1 |
cat | 1 |
mouse | 1 |
dog | 2 |
dog | 3 |
dog | 4 |
cat | 4 |
cat | 4 |
dog | 5 |
mouse | 5 |
I need to write the code in SQL. The logic in Alteryx's multi-row formula tool would be:
IF [ROW-1:Value] IS NULL AND [Value]='dog' THEN 1
ELSEIF [ROW-1:Value] IS NULL AND [Value]!='dog' THEN NULL()
ELSEIF [Value] = [ROW-1:Value] AND [Value]='dog' THEN [Row-1:ID] + 1
ELSE [ROW-1:ID]
ENDIF
What's the corresponding way to do this in SQL?
I know I can reproduce the table and join it to itself, offset (i.e. "moved down") by 1 row. Then I could compare values in table A with values in table B, and create a new column with the Alteryx-like logic I outlined above. That's a lot of steps though. Is there an easier way to do it in SQL? The CONDITIONAL_CHANGE_EVENT function looks almost like it will do what I need it to do, but not quite. I don't want to number rows within groups. I want to number the groups themselves.
Using
conditional_true_event()
(as suggested by Greg), and using an id to order by (as suggested by Mike):Which gives us results identical as the ones requested by the question.