Given the Date and Type columns, I want to create the column Group like in the table below where a Type of "A" marks the beginning of each group
| Date | Type | Group |
|---|---|---|
| 2019-09-26 | A | 1 |
| 2019-09-26 | B | 1 |
| 2019-10-09 | B | 1 |
| 2020-08-18 | A | 2 |
| 2020-09-25 | B | 2 |
| 2020-09-27 | B | 2 |
| 2021-02-19 | B | 2 |
| 2021-07-04 | A | 3 |
| 2021-08-04 | B | 3 |
| 2022-03-17 | A | 4 |
| 2022-05-01 | B | 4 |
| 2022-05-05 | B | 4 |
CodePudding user response：
Using CONDITIONAL_TRUE_EVENT windowed function:
Returns a window event number for each row within a window partition based on the result of the boolean argument expr1. The number starts from 0 and is incremented by 1 for each row on which the expr1 evaluates to true.
SELECT *, CONDITIONAL_TRUE_EVENT(Type='A') OVER(ORDER BY Date) 1 AS grp
FROM tab
ORDER BY Date;