Re: SQL to show window around selected records

Add a "unique" clause to that and it works perfectly. I didn't realize that the table reference could itself be a subquery. Having seen the answer now, it makes perfect sense.

Thanks!

"Scott Mattes" <Scott_at_TheMattesFamily.ws> wrote in message news:<AUR4a.11561$jR3.5975784_at_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 Thu Feb 20 2003 - 15:41:12 CST