Select rows starting from specified value until another specified value

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.

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:

• Using Common Table Expressions

• Using APPLY

• ROW_NUMBER (Transact-SQL)

StackExchange Database Administrators Q#158713, answer score: 7