Select rows starting from specified value until another specified value

Question

I have a table containing several columns and want to check for a column value, select the rows after that column value until a separate column value shows up. I have tried using BETWEEN, but if the column values are integers it only looks for numbers between the integers.

For example, if I have a table like this:

id  time      value 
t1  12:00PM   15
t1  12:02PM   4
t1  12:03PM   7
t1  12:05PM   16
t5  12:10PM   250
t5  12:15PM   15
t8  11:00AM   15
t8  3:00PM    2
t8  3:05PM    100
t2  7:00PM    15
t2  7:01PM    16
t15 5:00AM    35

I would want to get the rows between values of 15 and 16. Basically, if I could sort by id, then time, and gather the rows after 15 appears until there is a value of 16 within that same id. If there is no value 16, I would want the next 100 rows for example and then search for the next value of 15.

I would like the query to return this:

id  time      value 
t1  12:00PM   15
t1  12:02PM   4
t1  12:03PM   7
t1  12:05PM   16
t2  7:00PM    15
t2  7:01PM    16
t5  12:15PM   15
t8  11:00AM   15
t8  3:00PM    2
t8  3:05PM    100

That may be confusing. I've tried:

SELECT * FROM table WHERE value BETWEEN '15' AND '16' ORDER BY id, time

as a starting point, but that only returns rows with values of 15 or 16 because they are integers.

I want to sort the table by id then time. These entries are added automatically via another system so I'm trying to query the table for specific value ranges.

Any ideas?

Clarification:

If I had rows with 15, 1, 16, 7, 15, 2, 16 for the same id, I would want both "islands": 15, 1, 16, 15, 2, 16.

score 7 · Accepted Answer · edited Dec 20 '16 at 19:26

A suggestion that should work in 2008 version.

Tested at rextester.com:

with 
  end_points as                       -- find start and end points
    ( select id, time, value
      from table_x
      where value in (15, 16)
    ), 
  start_points as                     -- only the start points
    ( select id, time, value
      from end_points
      where value = 15
    )
select 
    t.id, t.time, t.value
from
    start_points as s
  outer apply                         -- find where each island ends
    ( select top (1) ep.* 
      from end_points as ep
      where s.id   = ep.id
        and s.time < ep.time
      order by ep.time
    ) as e
  cross apply                         -- and run through each island
    ( select p.id, p.time, p.value, 
             rn = row_number() over (order by p.time) 
      from table_x as p
      where s.id   = p.id
        and s.time <= p.time
        and         ( p.time < e.time
                   or p.time = e.time and e.value = 16
                   or          e.time is null)
    ) as t
where 
    t.rn <= 100
order by
    t.id, t.time  ;

More info:

David דודו Markovitz · Answer 2 · 2016-12-20T17:45:13.820

select id,time,value

from   (select  *
               ,min (case when value = 16 then rn end) over 
                (
                    partition by    id,grp_id
                ) as min_rn_16

        from   (select  *
                       ,row_number () over 
                        (
                            partition by    id,grp_id 
                            order by        time
                        ) as rn

                from   (select      *
                                   ,count (case when value = 15 then 1 end) over 
                                    (
                                        partition by    id 
                                        order by        time 
                                        rows            unbounded preceding
                                    ) as grp_id

                        from        mytab
                        ) t

                where   grp_id > 0
                ) t

        where   rn <= 100
        ) t

where   rn <= coalesce (min_rn_16,100)

Select rows starting from specified value until another specified value

2 Answers2