8

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.

  • How do you want the solution to handle a case where a 15 was found and after some values that are not 16, there is another 15 value? – David דודו Markovitz Dec 20 '16 at 23:16
  • @DuduMarkovitz After 100 values if no value of 16 was found, I would want to start over at the next 15 value. If there is another value of 15 after less than 100 values, it should start over again. For my purposes, this should rarely happen if at all. It is a valid question though. – Link Dec 21 '16 at 14:21
  • Thanks. luckily this is the way it is already handled by the current solutions. – David דודו Markovitz Dec 21 '16 at 14:23
7

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:

1
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)
  • 2
    It's in 2008 version. No COUNT(*) OVER (ORDER BY ..) allowed. – ypercubeᵀᴹ Dec 20 '16 at 16:59
  • Looks great and makes me realize I need to read a little more to understand what all of this does. Thanks for the input. – Link Dec 20 '16 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.