Re: best way to select from a range of values?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 5 Jul 2009 23:02:58 -0700 (PDT)
Message-ID: <5cdab4ae-18cb-4044-ac5b-37dfb9666621_at_g1g2000yqh.googlegroups.com>

On 6 Jul., 03:42, m..._at_pixar.com wrote:
> given this table:
>
> x   y
> --  -
> 10  a
> 20  b
> 30  c
>
> I want the best way to to map values [10,20) -> a
>                                      [20,30)] -> b
>                                      [30,inf) -> c
>
> Right now I'm using a query like:
>
>     select y from foo
>      where x=(select max(x) from foo
>                where x<=21);
>
> Is there a better way to do this?  Is there an analytic function
> that might help?
>
> Many TIA!
> Mark
>
> create table foo as
> select 10 as x ,'a' as y from dual union
> select 20,'b' from dual union
> select 30,'c' from dual;
>
> -- returns: a,b,b:
> select y from foo where x=(select max(x) from foo where x<=19);
> select y from foo where x=(select max(x) from foo where x<=20);
> select y from foo where x=(select max(x) from foo where x<=21);
>
> --
> Mark Harrison
> Pixar Animation Studios

Given the x in foo is unique you can indeed use *aggregate* function last/first (however, i would cautious to claim - it is the best way, it depends as usual)

SQL> select max(y) keep (dense_rank last order by x) from foo where x<=&x;
Enter value for x: 19
old 1: select max(y) keep (dense_rank last order by x) from foo where x<=&x
new 1: select max(y) keep (dense_rank last order by x) from foo where x<=19

M
-
a

SQL> select max(y) keep (dense_rank last order by x) from foo where x<=&x;
Enter value for x: 20
old 1: select max(y) keep (dense_rank last order by x) from foo where x<=&x
new 1: select max(y) keep (dense_rank last order by x) from foo where x<=20

M
-
b

SQL> select max(y) keep (dense_rank last order by x) from foo where x<=&x;
Enter value for x: 21
old 1: select max(y) keep (dense_rank last order by x) from foo where x<=&x
new 1: select max(y) keep (dense_rank last order by x) from foo where x<=21

M
-
b

More examples in datawarehousing guide
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#sthref1757

Best regards

Maxim Received on Mon Jul 06 2009 - 01:02:58 CDT