Lookup LEAD and LAG in the SQL reference manual for 9i. LEAD and LAG
should do exactly what you want.
--
Anderw Allen
Brett Hunsaker wrote:
> I'd like to display records both preceding and following a record in an Oracle
> 9i database.
>
> Given a table named EVENTS with the following layout:
>
> Name Null? Type
> ------------------ -------- --------------
> LOGSEQUENCE NOT NULL NUMBER(38)
> LOGTIMESTAMP NOT NULL DATE
> DESCRIPTION VARCHAR2(255)
>
> I want to display the 4 records preceding and following a record (when
> sorted by the LogSequence column) where the description text begins with
> "BAD THING HAPPENED".
>
> select * from events where description like 'BAD THING HAPPENED%'
> order by logsequence;
>
> gets the center record.
>
> 'LogSequence' is a monotonically increasing number.
>
> Can this be done in a single SQL statement? Examples would be appreciated.
>
> Thanks!
Received on Wed Feb 19 2003 - 15:40:59 CST
