How do I get the rows between two conditions in an sql table?

Question

say I have a table like this

number      status
----------------------
 1          g
 2          a
 3          b
 4          c
 5          d
 6          e
 7          f
 8          b
 9          e
 10         a
 11         c
 12         f
 13         g
 14         d
 15         a

I want to get all the rows from a to f (but not f to a) so that the resultant object is like this. How do I do this? Thanks!

number      status
----------------------
 2          a
 3          b
 4          c
 5          d
 6          e
 7          f
 10         a
 11         c
 12         f

Answer 1

This should work with any 2 letters (assuming they are not the same i.e. f to f). The way it works:

1. First cte marks where the sequence starts - in this case 'a' - and where it should end - any letter after the end sequence letter.
2. Once these letters are marked you can cumulative sum to get the groupings of sequences.
3. Finally get the first and last values of these groups to determine which groups are in the desired sequence.

Here is a working example: DB Fiddle

with cte as (
  select *,
  case when status = 'a' or lag(status) over(order by number) = 'f' then 1 else 0 end mrk
  from t
), cte2 as (
  select *,
  sum(mrk) over(order by number) grp
  from cte
), cte3 as (
  select *,
  first_value(status) over(partition by grp) firstval,
  last_value(status) over(partition by grp) lastval
  from cte2
)

select number,status
from cte3
where firstval = 'a' and lastval = 'f'

Answer 2

with
    t as ( -- example data
        select *
        from unnest(string_to_array('gabcdefbeacfgda', null)) with ordinality as t(v,i)),
    r as ( -- find desired ranges
        select
            min(i) as ia,
            (select min(i) from t as tf where tf.v = 'f' and ta.i < tf.i) as if
            from t as ta
            where v = 'a' group by if)
select * from t join r on (t.i between r.ia and r.if) order by i;

┌───┬────┬────┬────┐
│ v │ i  │ ia │ if │
├───┼────┼────┼────┤
│ a │  2 │  2 │  7 │
│ b │  3 │  2 │  7 │
│ c │  4 │  2 │  7 │
│ d │  5 │  2 │  7 │
│ e │  6 │  2 │  7 │
│ f │  7 │  2 │  7 │
│ a │ 10 │ 10 │ 12 │
│ c │ 11 │ 10 │ 12 │
│ f │ 12 │ 10 │ 12 │
└───┴────┴────┴────┘

Answer 3

Your sample data has some patterns that might simplify the logic:

• Two "a"s never appear in sequence without an intervening "f".
• Two "f"s never appear in sequence without an intervening "a".

With this assumption you can do a cumulative sum of "a"s minus "f"s and basically use that to identify the groups. You want the "f" and you don't want the final group, so the filtering logic is a little bit more complicated:

select t.number, t.status
from (select t.*,
             (count(*) filter (where status = 'a') over (order by number) -
              count(*) filter (where status = 'f') over (order by number rows between unbounded preceding and 1 preceding)
             ) as grp,
             min(number) filter (where status = 'f') over (order by number desc) as next_f
      from t
     ) t
where grp > 0 and next_f is not null;

Here is a db<>fiddle.

Answer 4

Schema:

 create table mytable (number int,      status varchar(10));
 insert into mytable values( 1          ,'g');
 insert into mytable values( 2          ,'a');
  insert into mytable values(3          ,'b');
  insert into mytable values(4          ,'c');
  insert into mytable values(5          ,'d');
  insert into mytable values(6          ,'e');
  insert into mytable values(7          ,'f');
  insert into mytable values(8          ,'b');
  insert into mytable values(9          ,'e');
  insert into mytable values(10        , 'a');
  insert into mytable values(11         ,'c');
  insert into mytable values(12         ,'f');
  insert into mytable values(13         ,'g');
  insert into mytable values(14         ,'d');
  insert into mytable values(15         ,'a');

Query:

 with cte as 
    (
        select number,status,(case when status='a' then grp else lag(grp) over(ORDER BY NUMBER)end)GRP from 
        (
            select *, lead(status)over(order by number)nextStatus,lag(status)over(order by number)prevStatus, number grp from mytable  
            where status='a'or  status='f'
        )t
        where (status='a' and nextStatus='f') or (status='f' and prevStatus='a')
    )
    select m.number,m.status from mytable m inner join 
    ( 
        select min(number) strt, max(number) fnsh
        from cte 
        group by grp                         
    )t 
    on m.number between t.strt and t.fnsh order by number
    

Output:

number	status
2	a
3	b
4	c
5	d
6	e
7	f
10	a
11	c
12	f

db<>fiddle here

Answer 5

You can find the minimum and maximum numbers corresponding with a and f for each row, and then use these values to get the valid rows between a and f:

with to_a as (select t1.*, 
                    (select max(t2.number) from t t2 where t2.number <= t1.number and t2.status = 'a') a,
                    (select max(t2.number) from t t2 where t2.number < t1.number and t2.status = 'f') f,
                    (select min(t2.number) from t t2 where t2.number > t1.number and t2.status = 'f') f1    
             from t t1)
select number, status from to_a where coalesce(a, 0) > coalesce(f, 0) and f1 is not null;