SQL Sorting With Overlap

Question

I have a table containing a list of indicies and values

OrigTable

Index     Value
  1        10
  2        11
  3        16
  4        18
  5        14
  6        11
  7        10
  8        12
  9        20
 10        17

I need to use two other tables to separate the values.

Table1 will contain rows from OrigTable where Value is <=15, and

Table2 will contain >15, but also containing one point in either direction such that if index n corresponds to a value above 15, it will be included in Table 2, but so will indicies n-1 and n+1

as such the result will

Table1:

Index    Value
  1        10
  2        11
  5        14
  6        11
  7        10
  8        12

Table2:

Index    Value
  2        11
  3        16
  4        18
  5        14
  8        12
  9        20
 10        17

I haven't been able to determine a way to do this using SQL. Is it not possible, or am I missing some command that would make this doable?

EDIT: 15 would be included in Table 1; I forgot to add 'or equal to'. By 'one point in either direction', I meant that if index n corresponds to a value above 15, it will be included in Table 2, but so will indicies n-1 and n+1

Answer 1

create table OrigTable
( 
theIndex int not null,
theValue int not null
);

insert into OrigTable (theIndex,theValue) values (1,10),(2,11),(3,16),(4,18),(5,14),(6,11),(7,10),(8,12),(9,20),(10,17);
-- select * from OrigTable;

create table table1
( 
theIndex int not null,
theValue int not null
);

create table table2
( 
theIndex int not null,
theValue int not null
);

insert into table1 (theIndex,theValue) select theIndex,theValue from OrigTable where theValue<=15;

insert into table2 (theIndex,theValue) select theIndex,theValue from OrigTable where theValue>15;

insert into table2 (theIndex,theValue) 
select a.theIndex,a.theValue 
from table2 b
join OrigTable a
on a.theIndex=b.theIndex-1
where a.theIndex not in (select theIndex from table2)

insert into table2 (theIndex,theValue) 
select a.theIndex,a.theValue 
from table2 b
join OrigTable a
on a.theIndex=b.theIndex+1
where a.theIndex not in (select theIndex from table2)



select * from table1 order by theIndex
select * from table2 order by theIndex

Answer 2

You just need one point, and it might not be 15. So, for the first query:

select t.*
from table t
where t.value <= 15

And for the second:

(select t.*
 from table t
 where t.value <= 15
 order by t.value desc
 limit 1
) union all
(select t.*
 from table t
 where t.value > 15
)