SEO

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

select *
from quote
where tms in (
select max(tms)
from quote
where tms <= to_date('&date','MM/DD/YYYY hh24:MI:SS')
union
select min(tms)
from quote
where tms >= to_date('&date','MM/DD/YYYY hh24:MI:SS')
)
/

Thanks
Jay

Hi,

"Jay" <me@heyjay.com> wrote in message
news:OOydnfokxroH6GPdRVn-qw@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

>
> 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
>

Thanks VC, I'll have to look up the "over" clause

Jay