CODEWITHSUNDEEP
sqlsql-serverrankpuzzleranking-functions
NIDHI MITTAL
NIDHI MITTAL

Reputation: 11

We need to provide rank. Logic is whenever we have a value 'Product' then we must start the new number & all other below should follow the same rank

Sample Input:

ID Vals
1 Product
2 Milk
3 Butter
4 Cheese
5 Yogurt
6 Product
7 Muesli
8 Porridge
9 Product
10 Banana

Sample Output:

ID Vals RANK
1 Product 1
2 Milk 1
3 Butter 1
4 Cheese 1
5 Yogurt 1
6 Product 2
7 Muesli 2
8 Porridge 2
9 Product 3
10 Banana 3

Upvotes: 0

Views: 553

Answers (4)

Himanshu Mishra
Himanshu Mishra

Reputation: 11

I tried it, and found the solution as below:

WITH RankedData AS (
    SELECT
        ID,
        Vals,
        ROW_NUMBER() OVER (ORDER BY ID) AS RowNum,
        CASE WHEN Vals = 'Product' THEN 1 ELSE 0 END AS IsProduct
    FROM
        RankingPuzzIe  -- Replace 'YourTableName' with your actual table name
)
SELECT
    ID,
    Vals,
    RowNum,
    IsProduct,
    SUM(IsProduct) OVER (ORDER BY RowNum) AS Rank
FROM
    RankedData
ORDER BY
    ID;

Output of Query

In case of Id column data is not same for same Vals then, in order to get desired output, we can go with this row_number(). I was asked in one of the discussion in my office.

Click here for usecase

Please check here for output for the usecase:

output for the usecase

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

you also can use window function:

SELECT * , SUM(CASE WHEN Vals ='Product' THEN 1 END) OVER (ORDER BY id) ranking 
FROM tableName

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This looks like a conditional cumulative sum:

select t.*,
       sum(case when vals = 'Product' then 1 else 0
           end) over (order by id) as ranking
from t
order by id;

Upvotes: 1

umberto-petrov
umberto-petrov

Reputation: 733

Assuming your table is sorted by ID:

SELECT T.ID, 
  T.Vals, 
  (SELECT COUNT(DISTINCT ID) FROM table WHERE Vals = 'Product' AND ID <= T.ID) as Rank
FROM table T

Upvotes: 0

Related Questions