Re: SQL question - find rows surrounding criteria

Hi,

"Jay" <me_at_heyjay.com> wrote in message
news:OOydnfokxroH6GPdRVn-qw_at_speakeasy.net...
> This may be an old question, but I'm not sure where to search
> the web to find my answer.
>
> I have a table with stock quotes. It looks like:
>
> id tms Price
> -----------------------------
> 1 1/1/04 08:00:00 50.00
> 2 1/1/04 17:00:00 51.13
> 3 1/2/04 08:00:00 50.85
> 4 1/2/04 17:00:00 49.40
> 5 1/3/04 08:00:00 48.90
> 6 1/3/04 17:00:00 47.13
> 7 1/4/04 08:00:00 50.10
> 8 1/4/04 17:00:00 51.75
>
> I'd like to retrieve the 2 stock quotes that are closest to the
> date I supply. I want one row that is the closest <= to the
> date I supply, and the other that is closest >= the date I supply.
>
> For example if supply "1/2/04 12:00:00" to the query, it would
> return rows:
>
> 3 1/2/04 08:00:00 50.85
> 4 1/2/04 17:00:00 49.40
>
> But if I supplied "1/2/04 18:00:00" it would return:
>
> 4 1/2/04 17:00:00 49.40
> 5 1/3/04 08:00:00 48.90
>
> The only way I can think to do it is like below, but its expensive
> when my table is very long

You can do this:

--
select id,tms,price from (
  select id, tms, price,
       row_number() over (order by case when delta >=0 then delta end)  rn1,
    row_number() over (order by case when delta <=0 then -delta end) rn2
  from (select id, tms, price, tms - to_date('1/2/04 18:00:00', 'mm/dd/yy
hh24:mi:ss') delta from quote)
) where rn1=1 or rn2=1

-- result:

ID TMS PRICE
4 1/2/2004 5:00:00 PM 49.4
5 1/3/2004 8:00:00 AM 48.9

VC