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?
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: