Re: Greatest value less than

You can make it slightly more efficient:

select a.key
from table a
where (a.level, a.property) = (
  select max(b.level), b.property
  from table b
  where b.property = p and

              b.level <= x)
;

<jkdufair_at_my-deja.com> wrote in message news:7odho6$bjs$1_at_nnrp1.deja.com...
> I have a table which is essentially:
>
> key level property
> --- ----- --------
> a 100 foo
> b 250 foo
> c 500 foo
> d 100 bar
> e 250 bar
> f 500 bar
>
> and, at runtime, I'll get a value, x, and a property, p. I need to find
> the key that corresponds to the largest value less than or equal to x
> and also to property p. I came up with the following query:
>
> select key
> from table
> where level = (select max(level)
> from table
> where level <= x and
> property = p) and
> property = p;
>
> This seems awkward to me. Is there a more elegant solution? TIA.
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Aug 09 1999 - 20:05:47 CDT