CODEWITHSUNDEEP
sqloracle-databasegroupingwindow-functionsranking-functions
cas s3
cas s3

Reputation: 23

Rank every x rows per group

I have a table for example (rank increase by one by every day):

Rank,day 
1  ,sunday 
1  ,sunday 
1  ,sunday 
1  ,sunday 
2  ,monday 
3  ,friday

and I want to add new column call 'group' that increase by 1 every 3 rows for each group. for example: ( 4 rows for day: sunday so the first 3 rows will be 1 and from row 4 to 6 its 2 and etc....):

Rank,group,day 
1 ,1 ,sunday 
1 ,1 ,sunday 
1 ,1 ,sunday 
1 ,2 ,sunday 
2 ,1 ,monday 
3 ,1 ,friday

How can I add the column group with plsql?

I cant use rownum because I used row_number function for the first column 'Rank'.

thanks.

EDIT: 07-NOV-18:

I want to add another criteria for the rank column called time. for example: 

Rank,group,day,time
1 ,1 ,sunday ,08:00 
1 ,1 ,sunday ,08:00
2 ,1 ,sunday ,09:00
3 ,2 ,sunday ,10:00
4 ,1 ,monday ,08:00
5 ,1 ,friday ,09:00
5 ,1 ,friday ,09:00

any advice?

Upvotes: 0

Views: 797

Answers (2)

cas s3
cas s3

Reputation: 23

Please check the Update from 07-NOV-2018.

Upvotes: 0

Boneist
Boneist

Reputation: 23578

You can do this by using the row_number() analytic function to assign a number to each row in the group. Then you can divide each row number by 3 and find the ceiling of that number. This means that the first three rows in the group will have a value of 1, the next three 2, etc:

WITH your_table AS (SELECT 1 rnk, 'sunday' dy FROM dual UNION ALL
                    SELECT 1 rnk, 'sunday' dy FROM dual UNION ALL
                    SELECT 1 rnk, 'sunday' dy FROM dual UNION ALL
                    SELECT 1 rnk, 'sunday' dy FROM dual UNION ALL
                    SELECT 2 rnk, 'monday' dy FROM dual UNION ALL
                    SELECT 3 rnk, 'friday' dy FROM dual)
SELECT rnk,
       dy,
       ceil(row_number() over (PARTITION BY rnk ORDER BY dy)/3) grp
FROM   your_table
ORDER BY rnk, dy, grp;

       RNK DY            GRP
---------- ------ ----------
         1 sunday          1
         1 sunday          1
         1 sunday          1
         1 sunday          2
         2 monday          1
         3 friday          1

Upvotes: 1

Related Questions