Finding number withing specific range

[Pankaj]

Hi,

I have a requirement where I need to check the lower and higher
"range" for specific number

Example:

Range:
0.05
0.1
0.15
0.2
0.25
0.3
0.35

So if I am provided with number say 0.29, I have to return below
outout

Number Low_range High_range
0.29 0.2 0.3
0.13 0.1 0.15

How can I do this via oracle sql? I tried couple of options but
nothing seems to work.

Any help would be appreciated

[Gerard H. Pille]

I can see that nothing works, even you fail!

Shouldn't the low range for 0,29 be 0,25 ???

In which case, for a given number N

select d.N, r1.range low_range, r2.range high_range
from
(select N from dual) d,
range_table r1,
range_table r2
where r1.range < d.N
and not exists (select range from range_table where range > r1.range and range < d.N)
and r2.range > d.N
and not exists (select range from range_table where range > d.N and range < r2.range)

Of course, this will not work if N does not fit into a range.

You could cover that with scalar selects.

select d.N,
(select range from range_table t1 where t1.range < d.N
and not exists (select range from range_table t2 where t2.range > t1.range and t2.range < d.N)) low_range,
(select range from range_table t1 where t1.range > d.N
and not exists (select range from range_table t2 where t2.range < t1.range and t2.range > d.N)) high_range
from
(select N from dual) d

[ddf]

And this is 'answered' in another group (comp.databases.oracle.server) in a similar fashion. I suppose he's looking for someone to actually do his work rather than do it himself.


David Fitzjarrell

[Gerard H. Pille]

ddf wrote:
>
> And this is 'answered' in another group (comp.databases.oracle.server) in a similar fashion. I suppose he's looking for someone to actually do his work rather than do it himself.
>
>
> David Fitzjarrell
>

That's what happens when you read the newsgroups alphabetically. But the immense gratitude he's shown, made it worth my while.

[Gerard H. Pille]

ddf wrote:
>
> And this is 'answered' in another group (comp.databases.oracle.server) in a similar fashion. I suppose he's looking for someone to actually do his work rather than do it himself.
>
>
> David Fitzjarrell
>

Sorry to have spoiled your fun.

[ddf]

Nothing spoiled here. My 'note' was really to the OP, not you.


David Fitzjarrell