CODEWITHSUNDEEP
sqloracle-database
DRTauli
DRTauli

Reputation: 771

Get ranges of an ordered set of rows in oracle

I have this table.

Seq key 1 A 2 A 3 A 4 A 5 B 6 C 7 C 8 C 9 C 10 A 11 A

I want to query this and return a range of the key in order.

key strt end A 1 4 B 5 5 C 6 9 A 10 11

I tried doing a min and max grouped by key but this does not consider the last A.

select key, min(seq), max(seq) from tblname group by key order by min(seq)

I also tried to add another column just to uniquely identify the second set of A but i don't really want to add another column. Note Seq here is unique and in an ordered sequence.

Is there a way to do this with only SQL? No procedures. Thanks in Advance

Upvotes: 0

Views: 38

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40499

select
  key_,
  min(seq),
  max(seq)
from (
  select
    key_,
    seq,
    sum(case when key_ = l then 0 else 1 end) over (order by seq) s
  from (
    select 
      key_,
      seq,
      lag(key_) over (order by seq) l
    from
      tq84_  /* Insert table name here */
  )
)
group by
  key_,
  s
order by
  min(seq);

Upvotes: 1

jarlh
jarlh

Reputation: 44696

select key,
       seq,
       (select max(seq) from ts t2
        where seq >= t1.seq
          and not exists (select 1 from ts
                          where seq between t1.seq and t2.seq
                            and key <> t1.key))
from ts t1 where not exists (select 1 from ts
                             where key = t1.key
                               and seq = t1.seq - 1);

Executes as:

SQL>create table ts (seq int, key char(1));
SQL>insert into ts values (1,'A');
SQL>insert into ts values (2,'A');
SQL>insert into ts values (3,'A');
SQL>insert into ts values (4,'A');
SQL>insert into ts values (5,'B');
SQL>insert into ts values (6,'C');
SQL>insert into ts values (7,'C');
SQL>insert into ts values (8,'C');
SQL>insert into ts values (9,'C');
SQL>insert into ts values (10,'A');
SQL>insert into ts values (11,'A');
SQL>select key,
SQL&       seq,
SQL&       (select max(seq) from ts t2
SQL&        where seq >= t1.seq
SQL&          and not exists (select 1 from ts
SQL&                          where seq between t1.seq and t2.seq
SQL&                            and key <> t1.key))
SQL&from ts t1 where not exists (select 1 from ts
SQL&                             where key = t1.key
SQL&                               and seq = t1.seq - 1);

key            seq
===            ===
A                1           4
B                5           5
C                6           9
A               10          11

4 rows found

Upvotes: 1

Related Questions