Find closest or higher values in SQL

I have a table:

table1

rank  value
1     10
25    120
29    130
99    980

I have to generate the following table:

table2

rank  value
1     10
2     10
3     10
4     10
5     10
6     10
7     10
8     10
9     10
10    10
11    10
12    10
13    120
14    120
15    120
.
.
.
.
25    120
26    120
27    130
28    130
29    130
30    130
.
.
.
.
.
62    980
63    980
.
.
.
99    980
100   980

So, table2 should have all values from 1 to 100. There are 3 cases:

• If it’s an exact match, for ex. rank 25, value would be 120
• Find closest, for ex. for rank 9 in table2, we do NOT have exact match, but 1 is closest to 9 (9-1 = 8 whereas 25-9 = 16), so assign value of 1
• If there is equal distribution from both sides, use higher rank value, for ex. for rank 27, we have 25 as well as 29 which are equally distant, so take higher value which is 29 and assign value.

Answer

something like

-- your testdata
with table1(rank,
value) as
 (select 1, 10
    from dual
  union all
  select 25, 120
    from dual
  union all
  select 29, 130
    from dual
  union all
  select 99, 980
    from dual),
-- range 1..100
data(rank) as
 (select level from dual connect by level <= 100)

select d.rank,
       min(t.value) keep(dense_rank first order by abs(t.rank - d.rank) asc, t.rank desc)
  from table1 t, data d
 group by d.rank;