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
.