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

On 6 Jul., 03:42, m... (AT) pixar (DOT) com wrote:
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/B...htm#sthref1757

Best regards

Maxim