Creating sequentially increasing groups based on number change

Question

How can I code this in oracle SQL? I have the below data Current Result

I want to generate a result that looks like the following: Desired Result

So, I essentially want the group ID to increase as the row number changes back to 1. I am trying to use row_number, rank() and partition functions but it is not working properly. Desperate for help! Thanks

EDIT (by Gordon):

The original question had the data in question. It is much, much better to have the values in the question as text than to refer to an image, so I'm adding it back in:

Code   Row Number
214     1
214     2
210     1
210     2
210     3
214     1

I want to generate a result that looks like the following:

Code   Row Number  Group Id
214     1          1
214     2          1
210     1          2
210     2          2
210     3          2
214     1          3

SQL tables are inherently unordered. What column specifies the ordering? — Gordon Linoff, Nov 05 '15 at 22:36
The Row Number column repeats values. It does *not* specify the ordering of the rows. — Gordon Linoff, Nov 05 '15 at 23:20

score 0 · Accepted Answer · answered Nov 05 '15 at 22:37

0

In order to do what you want, you need a column that specifies the ordering of the rows in the table. Let me assume that you have an id or creation date or something similar.

If so, then what you want is simply a cumulative sum of the number of times that the second column is 1:

select t.*,
       sum(case when RowNumber = 1 then 1 else 0 end) over (order by id) as GroupId
from t;

answered Nov 05 '15 at 22:37

Gordon Linoff

1,242,037
58
646
786

So, can you clarify I am an oracle sql newbie can you show me what the results would look like. – user3056152 Nov 05 '15 at 22:39
@user3056152 . . . They would look exactly like the second table in your question (although there might be more columns, depending on what `t` actually looks like). – Gordon Linoff Nov 05 '15 at 22:42
it works for me... you need additional ID column to sort your result – are Nov 05 '15 at 23:03
Thank you all I think my issue was that the row number column numbering was not restarting on the code change. Does anyone have any insight on how to make that happen. – user3056152 Nov 05 '15 at 23:23

score 0 · Answer 2 · answered Nov 05 '15 at 22:59

it's still not clear which field is ID because if it's rownumber as you said it's not going work the way that you have in expected output

create table test (id int , code int, rownumber int);
insert into test values (1,214,1);
insert into test values (2,214,2);
insert into test values (3,210,1);
insert into test values (4,210,2);
insert into test values (5,210,3);
insert into test values (6,214,1);


select s.code,  sum(add_group) over (order by id) from (
select id, code, case when rownumber=1 then 1 else 0 end as add_group from test
order by id
) s

    CODE    SUM(ADD_GROUP)OVER(ORDERBYID)
1   214 1
2   214 1
3   210 2
4   210 2
5   210 2
6   214 3

btw the asnwer of @Gordon Linoff works better and exactly as you want but you need add additional field for order by

Thank you, you are right it did work the data was slightly different in my actual data set that is why it looked like it was not working. — user3056152, Nov 05 '15 at 23:24

Creating sequentially increasing groups based on number change

2 Answers2