Ok, this will be a two parter - I'll introduce you to the frustration that is "answer my question".

Part 1 - the question and my response and their initial response.

So, first the question - written in to me as a letter to the editor regarding a column I wrote on using rownum:

Hi,

I have a question about rownum which is not mentioned.

Lets say that I want to extract a 3 records out of a result where the middle one is the query. For example I have records:

ID Value
1 Hello
2 Hi
3 Wow
4 Shrek
5 Shus
6 What?

And I need a query that by receiving ID, it will extract the record with the id and the previous one to that and the next one. For example for selecting ID=4, it will return records: 3,4,5.

Is there a way to do it in Oracle?

Seems straightforward, concise, clear - and a question I've received numerous times before.  Looks like someone trying to emulate an ISAM library - porting some code to Oracle.  So - I respond:

This presumes that ID is "unique"

select * 
  from 
( select * 
    from t 
   where id >= (select nvl(max(id),0)  
                  from t 
                 where id < :id) 
   order by id) 
 where rownum <= 3;

For example, if you copy all_Objects into a table T and add a unique/primary key constraint on object_id:

select * 
  from ( select * 
           from t where object_id >= 
                   (select nvl(max(object_id),0) 
                      from t 
                     where object_id < :id) 
          order by object_id) 
 where rownum <= 3
 order by object_id

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2          0           0
Fetch        2      0.00       0.00          0          5          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           3

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  COUNT STOPKEY (cr=7 pr=0 pw=0 time=333 us)
      3   VIEW  (cr=7 pr=0 pw=0 time=307 us)
      3    TABLE ACCESS BY INDEX ROWID T (cr=7 pr=0 pw=0 time=270 us)
      3     INDEX RANGE SCAN T_PK (cr=5 pr=0 pw=0 time=282 us)(object id 55414)
      1      SORT AGGREGATE (cr=2 pr=0 pw=0 time=154 us)
      1       FIRST ROW  (cr=2 pr=0 pw=0 time=104 us)
      1        INDEX RANGE SCAN (MIN/MAX) T_PK (cr=2 pr=0 pw=0 time=85 us)(object id 55414)

Basically, we start by finding the row in 'front' of the one we need in the middle - the select max(id) does that bit for us and then starting with that record - retrieve in sorted order that record and the next two (rownum <= 3). 

A logical extension of the article - pretty efficient approach and answers the question.  (note; I added nvl(max(id),0) - the NVL bit after the fact this morning.... so that it 'works' for the first record as well - presuming that ID is a whole number)

OR DOES IT?

Apparently, it does not - the response was:

Hello Thomas,

Thanks for answering me.

This doesn?t help me, ....

Anyone want to guess why this didn't help them? It can be summarized in a single sentence - which I'll publish later.