Re: SQL to show window around selected records

From: Scott Mattes <Scott_at_TheMattesFamily.ws>
Date: Wed, 19 Feb 2003 20:35:44 GMT
Message-ID: <AUR4a.11561$jR3.5975784@news1.news.adelphia.net>

How about

select *
from events a,

( select logsequence from events where description like 'BAD THING HAPPENED%' ) b
where a.logsequence between (b.logsequence - 4 ) and (b.logsequence + 4 ) order by a.logsequence

"Brett Hunsaker" <brett-hunsaker_at_automation-software.com> wrote in message news:436f92d1.0302191108.4a41db9d_at_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 - 14:35:44 CST