CODEWITHSUNDEEP
sql
Vinayak kulkarni
Vinayak kulkarni

Reputation: 26

increase rank based on particular value in column

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

Upvotes: 0

Views: 88

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

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

Upvotes: 1

Related Questions