Finding number withing specific range

[Pankaj]

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

[Charles Hooper]

Pankaj,

It is generally best to show what you have already tried when posting a request for help. If I am understanding your problem correctly, there is an error in your sample output; the 0.2 should be 0.25.

There are a couple of ways to solve the problem. One method is to use the LEAD analytic function to allow the LOW_RANGE and HIGH_RANGE (the value returned by LEAD) values to be returned on the same row. Once that is done, the output may be slid into an inline view and then you can test to determine if the specific number to be checked (0.29, 0.13) is BETWEEN a specific LOW_RANGE and HIGH_RANGE pair in the inline view.

See:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions074.htm

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Jonathan Lewis]

"Pankaj" <harpre...@gmail.com> wrote in message
news:2f9bb59a-e348-4506...@e13g2000vbn.googlegroups.com...

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

[ddf]

There is an issue with a BETWEEN/lead solution when the submitted value matches an upper bound (which then becomes the next row's lower bound) as two rows are returned. It may be best to code a greater than or equal two on the lower range and a less than on the upper range to return the one 'proper' row. I won't post my example as that would provide the solution to the OP; know that BETWEEN will return two rows for a boundary value match.


David Fitzjarrell

[ddf]

You're getting help, you're simply not getting the work done for you. Posting to another group won't likely get you any further as the same people monitor all of the comp.databases.oracle newsgroups.

Post what you've done and we'll assist -- we won't do the work for you.


David Fitzjarrell