SQL to show window around selected records

From: Brett Hunsaker <brett-hunsaker_at_automation-software.com>
Date: 19 Feb 2003 11:08:34 -0800
Message-ID: <436f92d1.0302191108.4a41db9d@posting.google.com>

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 - 13:08:34 CST