CODEWITHSUNDEEP
sqloracle-database
Adam Nowak
Adam Nowak

Reputation: 77

How to get a group starting at the specified id

How to get a group of kol_name starting with the id that gets the ending when the 'status' = 'start' with it included. It`s mean, I gets 'id' from outside and wants to get a group of columns from the 'start' inclusive, to the next 'start' without him , or last ' add' , the result is used in a dynamic SQL 

id      kol_name      status
------------------------------
44     kol_name_1      add
43     kol_name_2      add
42     kol_name_3      add  
41     kol_name_4     start 
40     kol_name_5      add
39     kol_name_6     start 
37     kol_name_7     start

My expected output is when i get id 44 

kol_name
--------------------
kol_name_1      
kol_name_2      
kol_name_3       
kol_name_4

My expected output is when i get id 37 

kol_name
--------------------   
kol_name_7

My expected output is when i get id 39 

kol_name
--------------------   
kol_name_5 
kol_name_6

how can I do that?

Upvotes: 2

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Hmmm. One way is to calculate the first start "after" the given id ("after" seems to be a smaller id, which is why I put it in quotes). Then, you can just use simple logic:

select t.*
from t cross join
     (select max(id) as startid
      from t
      where id <= 44 and status = 'start'
     ) tt
where t.id <= 44 and t.id >= tt.startid;

If you only wanted to mention "44" once in the query:

select t.*
from t cross join
     (select theid, max(id) as startid
      from t cross join (select 44 as theid from dual)
      where id <= theid and status = 'start'
     ) tt
where t.id <= tt.theid and t.id >= tt.startid;

EDIT:

This code only returns one row:

with t as (
    select 44 as id, 'kol_name_1' as kol_name, 'add' as status from dual union all
    select 43, 'kol_name_2', 'add' from dual union all
    select 42, 'kol_name_3', 'add' from dual union all
    select 41, 'kol_name_4', 'start' from dual union all
    select 40, 'kol_name_5', 'add' from dual union all
    select 39, 'kol_name_6', 'start' from dual union all
    select 37, 'kol_name_7', 'start' from dual 
   )
select t.*
from t cross join
     (select max(id) as startid
      from t
      where id <= 39 and status = 'start'
     ) tt
where t.id <= 39 and t.id >= tt.startid;

Here is a SQL Fiddle

EDIT II;

Oh, you want two rows with 39. If that is the case, change the definition of startid using no equality:

select t.*
from t cross join
     (select max(id) as startid
      from t
      where id < 44 and status = 'start'
     ) tt
where t.id <= 44 and t.id >= tt.startid;

Upvotes: 1

Related Questions