SQL question - find rows surrounding criteria

From: Jay <me_at_heyjay.com>
Date: Wed, 21 Jul 2004 08:54:03 -0500
Message-ID: <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

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 Received on Wed Jul 21 2004 - 08:54:03 CDT