## Forum Stats

• 3,825,220 Users
• 2,260,482 Discussions
• 7,896,450 Comments

Discussions

# Assign group value based on lag value change

Member Posts: 311
edited Feb 20, 2010 5:21PM
I have some old data that has a structure determined by order. The groups are in ascending order, based that the amount column, and delineated by when the previous value is less than the current value. I just need to assign a unique group ID to each group. This is what I have:
```Order	Amount	Group
1	56
2	63
3	41
4	52
5	59
6	74
7	39
8	42
9	44
10	51
11	62
12	77```
and this is what I would like:
```Order	Amount	Group
1	56	1
2	63	1
3	41	2
4	52	2
5	59	2
6	74	2
7	39	3
8	42	3
9	44	3
10	51	3
11	62	3
12	77	3```
I can get the previous value with the lag function but am struggling with assigning the number. I am a sql more than pl/sql person. TIA.

## Answers

• Member Posts: 235
Something very similiar was up a few days ago. See this thread:

1033531
• Member Posts: 19,456 Red Diamond
```with t as (
select 1 ord,56 amount from dual union all
select 2,63 from dual union all
select 3,41 from dual union all
select 4,52 from dual union all
select 5,59 from dual union all
select 6,74 from dual union all
select 7,39 from dual union all
select 8,42 from dual union all
select 9,44 from dual union all
select 10,51 from dual union all
select 11,62 from dual union all
select 12,77 from dual
)
select  ord,
amount,
sum(start_of_group) over(order by ord) grp
from  (
select  ord,
amount,
case
when lag(amount,1,amount + 1) over(order by ord) > amount then 1
end start_of_group
from  t
)
order by ord
/

ORD     AMOUNT        GRP
---------- ---------- ----------
1         56          1
2         63          1
3         41          2
4         52          2
5         59          2
6         74          2
7         39          3
8         42          3
9         44          3
10         51          3
11         62          3

ORD     AMOUNT        GRP
---------- ---------- ----------
12         77          3

12 rows selected.

SQL> ```
SY.
• Member Posts: 8,424 Bronze Crown
```SQL> with t as (
select 1 o, 56 amount from dual union all
select 2, 63 from dual union all
select 3, 41 from dual union all
select 4, 52 from dual union all
select 5, 59 from dual union all
select 6, 74 from dual union all
select 7, 39 from dual union all
select 8, 42 from dual union all
select 9, 44 from dual union all
select 10, 51 from dual union all
select 11, 62 from dual union all
select 12, 77 from dual
)
---
---
select o "ORDER", amount, sum (grp) over (order by o) + 1 "GROUP"
from (select t.*,
case
when lag (amount) over (order by o) > amount then 1
else 0
end grp
from t) t
order by o
/
ORDER     AMOUNT      GROUP
---------- ---------- ----------
1         56          1
2         63          1
3         41          2
4         52          2
5         59          2
6         74          2
7         39          3
8         42          3
9         44          3
10         51          3
11         62          3
12         77          3

12 rows selected.```
• Member Posts: 311
Thanks. All of these solutions solve the problem.
• Member Posts: 16,087 Gold Crown
I stumbled upon this question and played a bit with it and I'm wondering what point I'm missing.
On first thought I'd wanted to update T using the UPDATE A JOIN technique (as a small 'weekend-exercise' ;-) ), but I keep on hitting ORA-01779 although I added a PK constraint and all columns of T are updatable/insertable/deletable. I tried several ways, below is the last effort, but all to no luck.
But using the same query with MERGE works without any problem.
What point am I missing? Perhaps it's very obvious, but as to now I don't really get it.
Could it be the analytics?

See this example:
```SQL> drop table t;

Table dropped.

SQL> create table t as
2  select 1 col_order, 56 amount from dual union all
3  select 2, 63 from dual union all
4  select 3, 41 from dual union all
5  select 4, 52 from dual union all
6  select 5, 59 from dual union all
7  select 6, 74 from dual union all
8  select 7, 39 from dual union all
9  select 8, 42 from dual union all
10  select 9, 44 from dual union all
11  select 10, 51 from dual union all
12  select 11, 62 from dual union all
13  select 12, 77 from dual;

Table created.

SQL> alter table t add constraint t_pk primary key (col_order);

Table altered.

SQL> alter table t add (grp number);

Table altered.

SQL> select t.*
2  from   all_updatable_columns t
3  where  t.table_name = 'T';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
HOEK                           T                              COL_ORDER                      YES YES YES
HOEK                           T                              AMOUNT                         YES YES YES
HOEK                           T                              GRP                            YES YES YES

3 rows selected.

SQL> select * from t;

COL_ORDER     AMOUNT        GRP
---------- ---------- ----------
1         56
2         63
3         41
4         52
5         59
6         74
7         39
8         42
9         44
10         51
11         62
12         77

12 rows selected.

SQL> update(
2         select t1.grp
3         ,      t2.new_grp
4         from   t t1
5         ,    ( select col_order
6                ,      sum(ranges) over (order by col_order) new_grp
7                from ( select col_order
8                       ,      case
9                                when lag(amount) over (order by col_order) < amount
10                                then 0
11                                else 1
12                              end  ranges
13                       from   t
14                     )
15              ) t2
16         where  t1.col_order = t2.col_order
17        )
18  set grp = new_grp;
set grp = new_grp
*
ERROR at line 18:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> select * from t;

COL_ORDER     AMOUNT        GRP
---------- ---------- ----------
1         56
2         63
3         41
4         52
5         59
6         74
7         39
8         42
9         44
10         51
11         62
12         77

12 rows selected.

SQL> merge into t
2  using ( select col_order
3          ,      sum(ranges) over (order by col_order) new_grp
4          from ( select col_order
5                 ,      case
6                          when lag(amount) over (order by col_order) < amount
7                          then 0
8                          else 1
9                        end  ranges
10                 from   t
11               )
12        ) t2
13  on (t.col_order = t2.col_order)
14  when matched then update set grp = new_grp;

12 rows merged.

SQL> select * from t;

COL_ORDER     AMOUNT        GRP
---------- ---------- ----------
1         56          1
2         63          1
3         41          2
4         52          2
5         59          2
6         74          2
7         39          3
8         42          3
9         44          3
10         51          3
11         62          3
12         77          3

12 rows selected.

SQL> ```
• Member Posts: 394 Bronze Badge
edited Feb 20, 2010 5:21PM
Hi !

Also from curiosity i have done some testing too

I have test this stuff with
```create or replace view VV as
select <anything - also analytic functions> from (SUBQUERY) ..```
SUBQUERY is select statement from table with PK and .. everything is just OK with table, and for a view VV is table key preserved

then
If somewhere in SUBQUERY is an analytic function then view VV has no updatable columns from table in subquery..
if there is no analytic functions in SUBQUERY then there are updatable columns in VV

I don't think that i have seen something like this in documentation .. Interesting realy . But in Merge statement this is OK .. Hm ??

Have a nice Sunday ..
This discussion has been closed.