Re: Finding number withing specific range

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Tue, 23 Apr 2013 15:35:59 -0700 (PDT)
Message-ID: <b1a4a290-ebd1-493e-8fb6-03d6c8fc08b7_at_googlegroups.com>

On Tuesday, April 23, 2013 5:31:18 PM UTC-4, Pankaj wrote:
> 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

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. Received on Wed Apr 24 2013 - 00:35:59 CEST