CODEWITHSUNDEEP
sqloracle-databaselistgroup-bylag
Thanh Phan
Thanh Phan

Reputation: 11

SQL oracle group list number

Please help me: group list number

Please help me: group list number

Upvotes: 1

Views: 463

Answers (3)

user5683823
user5683823

Reputation:

In Oracle 12.1 and above, this is a simple application of the match_recognize clause:

with
  inputs ( column1, column2 ) as (
    select  1, 1000 from dual union all
    select  2, 2000 from dual union all
    select  3, 3000 from dual union all
    select  4, 6000 from dual union all
    select  5, 7500 from dual union all
    select  6,    0 from dual union all
    select  7,  500 from dual union all
    select  8,  600 from dual union all
    select  9,  900 from dual union all
    select 10, 2300 from dual union all
    select 11, 4700 from dual union all
    select 12,   40 from dual union all
    select 13, 1000 from dual union all
    select 14, 2000 from dual union all
    select 15, 4000 from dual
  )
-- End of simulated inputs (not part of the solution).
-- SQL query begins BELOW THIS LINE. Use actual table and column names.
select column1, column2, column3
from   inputs
match_recognize(
  order by column1
  measures match_number() as column3
  all rows per match
  pattern ( a b* )
  define b as column2 >= prev(column2)
)
order by column1    --  If needed.
;

OUTPUT:

   COLUMN1    COLUMN2    COLUMN3
---------- ---------- ----------
         1       1000          1
         2       2000          1
         3       3000          1
         4       6000          1
         5       7500          1
         6          0          2
         7        500          2
         8        600          2
         9        900          2
        10       2300          2
        11       4700          2
        12         40          3
        13       1000          3
        14       2000          3
        15       4000          3

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use window function to mark the point where column_2 restarts and use cumulative sum to get the desired result

Select column_1,
    Column_2,
    Sum(flag) over (order by column_1) as column_3
From (
    Select t.*,
        Case when column_2 < lag(column_2,1,0) over (order by column_1) then 1 else 0 end as flag
    From your_table t
    ) t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

A new group starts when the values descend. You can find the groups where they start using lag(). Then do a cumulative sum:

select t.*,
       1 + sum(case when prev_col2 < col2 then 0 else 1 end) over (order by col1) as grp
from (select t.*, 
             lag(col2) over (order by col1) as prev_col2
      from t
     ) t;

Upvotes: 2

Related Questions