Re: Finding number withing specific range

"Pankaj" <harpreet.noni_at_gmail.com> wrote in message news:2f9bb59a-e348-4506-9379-12c6137e848b_at_e13g2000vbn.googlegroups.com...
|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

I'd second all comments from Charles Hooper about you current approach, and the correction.
The current approach should include a build script for your sample data so that we don't fly off at a tangent to what you need.

Here's the outline for a solution to a problem that might be the one you want solved:

select

    *
from (

    (
    select max(range_col) low_range
    from range_table
    where range_col <= :input_value
    ) v1,
    (
    select min(range_col) high_range
    from range_table
    where range_col >= :input_value
    ) v1
)

Strategy: find the largest value that is not greater than the input, and the smallest value that is not less than the input.

Only suitable for one input value, and needs rethinking for joins involving the range.
Optimal performance comes from an index on (range_col) that allows the optimize to use a "range scan (min/max)"

-- 

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543