I would appreciate some help for below issue. I have below table
| id | items |
|---|---|
| 1 | Product |
| 2 | Tea |
| 3 | Coffee |
| 4 | Sugar |
| 5 | Product |
| 6 | Rice |
| 7 | Wheat |
| 8 | Product |
| 9 | Beans |
| 10 | Oil |
I want output like below. Basically I want to increase the rank when item is 'Product'. May I know how can I do that? For data privacy and compliance purposes I have modified the data and column names
| id | items | ranks |
|---|---|---|
| 1 | Product | 1 |
| 2 | Tea | 1 |
| 3 | Coffee | 1 |
| 4 | Sugar | 1 |
| 5 | Product | 2 |
| 6 | Rice | 2 |
| 7 | Wheat | 2 |
| 8 | Product | 3 |
| 9 | Beans | 3 |
| 10 | Oil | 3 |
I have tried Lag and lead functions but unable to get expected output
CodePudding user response：
Here is solution using a derived value of 1 or 0 to denote data boundaries SUM'ed up with the ROWS UNBOUNDED PRECEDING option, which is key here.
SELECT
id,
items,
SUM(CASE WHEN items='Product' THEN 1 ELSE 0 END) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) as ranks
FROM