Below enclosed is my requirement.

I want to increment the dense rank function with the cap of each 5 line items by the partition of seller_state and warehouse_id code. for more clarification I have attached sample data of my requirement kindly help me on same.

below mentioned queries are my tries.

CASE
         WHEN icta_amount < 0 THEN (DENSE_RANK() OVER (PARTITION BY seller_state ORDER BY seller_state,warehouse_id)) % 5
         WHEN icta_amount >= 0 THEN (DENSE_RANK() OVER (PARTITION BY seller_state ORDER BY seller_state,warehouse_id))% 5
       END AS DENSE_RANK,

if i add warehouse_id in partition clause in all the places i am getting only 1 don't know the meaning of that.

Thank you in advance.

CodePudding user response：

I'd start with a row_number partitioned by the seller_state and warehouse_id, floor that into groups of five, and then dense_rank over it:

SELECT seller_state, warehouse_id,
       DENSE_RANK() OVER (PARTITION BY seller_state, warehouse_id
                          ORDER BY seller_state, warehouse_id, FLOOR((rn - 1) / 5.0))
FROM   (SELECT seller_state, warehouse_id, 
               ROW_NUMBER() OVER (PARTITION BY seller_state, warehouse_id) AS RN
        FROM   mytable) t

SQLFiddle demo

CodePudding user response：

 with data as (
     select *, row_number() over (order by seller_state, warehouse_id) as rn
     from T
 )
 select seller_state, warehouse_id,
     sum(case when rn % 5 = 1 then 1 end)
         over (order by seller_state, warehouse_id, rn) as adj
 from data