Step in an ordered list of rows

Hi to all!

I was searching for "the perfect solution" for quite a common problem when working with SQL in a GUI development environment.

Who can tell me how to code a fast and reliable SQL Statement to return the next X or previous X (X >= 1) no of rows when a given row is known and an order by clause is used. Also assume that the the column(s) you use in the order by clause are not unique in the table and that you can't use any optimizer hints.

e.g.:

You have a table like:

CREATE TABLE client AS
(
ClientId NUMERIC PRIMARY KEY,
Name VARCHAR2(30),
matchcode VARCHAR2(15)
);

You have a select like:

SELECT ClientID, Name, Matchcode FROM Client ORDER BY Matchcode;

Now you fetch some rows and after a while you just want to step 3 rows back or to skip 3 rows in the given order.

Any help, comments or hits are welcome...

Dieter Oberkofler
Director of Engineering

LEADING BITS GmbH.               Tel             (+43-1) 586 76 11
Schleifmuehlgasse 5/17           Fax             (+43-1) 587 76 15
A-1040 Vienna                    E-mail     dtr_at_leadingbits.via.at
Austria                          Compuserve            100141.1314


------------------------------------------------------------------