5 Replies Latest reply on Apr 24, 2013 2:30 PM by 1005051

# Finding number within specific range

Hi,

I have a requirement where I need to check the lower and higher
"range" for specific number. The range is in increment of 0.05

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

Edited by: 1002048 on Apr 23, 2013 3:00 PM
• ###### 1. Re: Finding number within specific range
1002048 wrote:
Hi,

I have a requirement where I need to check the lower and higher
"range" for specific number. The range is in increment of 0.05

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 ``````
why is LOW_RANGE=0.2? Should it be 0.25, instead?
• ###### 2. Re: Finding number within specific range
>
I have a requirement where I need to check the lower and higher
"range" for specific number. The range is in increment of 0.05

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
>
This is one way
``````with q as (select .29 myNum from dual
union all select .13 from dual)
select myNum, (trunc((myNum * 2) * 10)/2)/10 minRange,
(round((myNum * 2) * 10)/2)/10 maxRange from q

MYNUM     MINRANGE     MAXRANGE
0.29     0.25     0.3
0.13     0.1     0.15``````
• ###### 3. Re: Finding number within specific range
Hi,

Welcome to the forum!

Oracle has two very handy built-in functions, FLOOR and CEIL, that return the closest integer no greater than, or no less than, a given number. Is there some way we can leverage these functions? Yes! They deal with ranges of 1.00, and you have ranges of .05, or 1/20 that size. So magnify your numbers by a factor of 20, use FLOOR and CEIL, and then shrink the results back by a factor of 20, like this:
``````SELECT  n
,     FLOOR (n * 20) / 20    AS low_range
,     CEIL  (n * 20) / 20    AS high_range
FROM     table_x
;``````
To test this, I created a test table:
``````CREATE TABLE     table_x
(
n     NUMBER
);

INSERT INTO table_x (n) VALUES (.29);
INSERT INTO table_x (n) VALUES (.13);
COMMIT;``````
You should post sample data like this whenever you have a question.

I got these results from that data:
```````        N  LOW_RANGE HIGH_RANGE
---------- ---------- ----------
.29        .25         .3
.13         .1        .15``````

By the way, it's better to post questions like this that are about the SQL language in the SQL and PL/SQL
See the FAQ for that forum {message:id=9360002}