Assign group value based on lag value change

325647

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.

576633

Something very similiar was up a few days ago. See this thread:

1033531

Solomon Yakobson

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.

MichaelS

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.

325647

Thanks. All of these solutions solve the problem.

Hoek

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> 

ttt

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 ..