CODEWITHSUNDEEP
sqloracle-databaseselect
user1663715
user1663715

Reputation: 396

Oracle first_value(PARTITION) break sequence

select * from grouping;

above query gives:

WORKING_DAYS,DATE_TYPE,SEQ_START
10/11/2013  ,L        ,0
10/10/2013  ,L        ,0
10/8/2013   ,L        ,1
10/6/2013   ,H        ,0
10/5/2013   ,H        ,0
10/4/2013   ,L        ,0
10/3/2013   ,L        ,0
10/2/2013   ,L        ,0
10/1/2013   ,L        ,0

I want to group these rows by SEQ_START so that when I run it should give following result. rank column should be WORKING_DAYS (or any other number) grouped by SEQ_START. Please notice that even though 10/10/2013 and 10/11/2013 have 0 for SEQ_START but still it should not have 10/1/2013 instead should have 10/10/2013 because on 10/8/2013, the sequence is broken (0 for SEQ_START).

WORKING_DAYS,DATE_TYPE,SEQ_START,rank
10/1/2013   ,L        ,0        ,10/1/2013
10/2/2013   ,L        ,0        ,10/1/2013
10/3/2013   ,L        ,0        ,10/1/2013
10/4/2013   ,L        ,0        ,10/1/2013
10/8/2013   ,L        ,1        ,10/8/2013
10/10/2013  ,L        ,0        ,10/10/2013
10/11/2013  ,L        ,0        ,10/10/2013

I have written following query which return is nearly what I expect but it is giving 10/10/2013 and 10/11/2013 a "0" value instead they should have 10/10/2013 as sequence is broken on 10/8/2013 which has 1 for SEQ_START 

select working_days,  
       date_type, 
       seq_start, 
       FIRST_VALUE(working_days)  OVER (PARTITION BY seq_start 
                                        ORDER BY working_days) "rank"
  from grouping 
 where date_type = 'L'

Upvotes: 1

Views: 380

Answers (1)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

select 
  working_days,  date_type, seq_start, 
  FIRST_VALUE(working_days) OVER (PARTITION BY grp ORDER BY working_days) "rank"
from (
  select 
    working_days, date_type, seq_start,
    sum(front) over(order by working_days) as grp
  from (
    select 
      working_days, date_type, seq_start,
      decode(seq_start, lag(seq_start)over(order by working_days), 0, 1) as front
    from t1
  )
)
where date_type = 'L'
order by 1

fiddle

Upvotes: 1

Related Questions