SQL – ROW_NUMBER – Retrieving Rows from the Midpoint

December 9, 2009

A question appeared in the comp.databases.oracle.server Usenet group a couple of years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/a8314487f8c414ac

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?

But the ids are not seqeuntial – it can verify from each record, for example:

ID      Value 
225     Hello 
228     Hi 
258     Wow 
240     Shrek 
259     Shus 
230     What?

 I offered the following:

Note: LAG and LEAD will collapse the result into a single row.  If that is not desired, you will need a different approach:
The set up:

CREATE TABLE T1 ( 
  C1 NUMBER(12), 
  C2 VARCHAR2(20)); 
 
INSERT INTO T1 VALUES (1,'Hello'); 
INSERT INTO T1 VALUES (2,'Hi'); 
INSERT INTO T1 VALUES (3,'Wow'); 
INSERT INTO T1 VALUES (4,'Shrek'); 
INSERT INTO T1 VALUES (5,'Shus'); 
INSERT INTO T1 VALUES (6,'What?');

A simple query using the ROW_NUMBER analytical function:

SELECT 
  C1, 
  C2, 
  ROW_NUMBER() OVER (ORDER BY C1) RN 
FROM 
  T1; 
 
        C1 C2                           RN 
---------- -------------------- ---------- 
         1 Hello                         1 
         2 Hi                            2 
         3 Wow                           3 
         4 Shrek                         4 
         5 Shus                          5 
         6 What?                         6

Sliding the above into an inline view to retrieve only those on either side of RN=4:

SELECT 
  T.C1, 
  T.C2 
FROM 
  (SELECT 
    C1, 
    C2, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T 
WHERE 
  T.RN BETWEEN (4 -1) AND (4 +1);
        C1 C2 
---------- ------ 
         3 Wow 
         4 Shrek 
         5 Shus

But, the above is not exactly what you need, unless C1 (your ID column) always starts at 1 and incements by 1.  Essentially listing the inline view twice with a join fixes the problem:

SELECT 
  T.C1, 
  T.C2 
FROM 
  (SELECT 
    C1, 
    C2, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T, 
  (SELECT 
    C1, 
    ROW_NUMBER() OVER (ORDER BY C1) RN 
  FROM 
    T1) T2 
WHERE 
  T2.C1=4 
  AND T.RN BETWEEN (T2.RN -1) AND (T2.RN +1); 
 
        C1 C2 
---------- ----- 
         5 Shus 
         4 Shrek 
         3 Wow

A quick test:

DELETE FROM 
  T1 
WHERE 
  C1=3;

The first method results in:

        C1 C2 
---------- ----- 
         4 Shrek 
         5 Shus 
         6 What?

The second method results in:

        C1 C2 
---------- ----- 
         5 Shus 
         4 Shrek 
         2 Hi

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A couple other people in the thread offered suggestions, including this one by Martin T.:
Maybe Analytic Functions can help:

select * from ( 
SELECT some_table.*,
 lead(ID) over (order by date) as LEAD_ID,
 lag(id) over (order by date) as LAG_ID 
from some_table 
) some_table_plus 
where ID      = :search_id 
    or LEAD_ID = :search_id 
    or LAG_ID  = :search_id 
order by date