Re: oracle rownum

On Aug 21, 10:11 am, odelya <be.spec..._at_gmail.com> wrote:
> 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?

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

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Aug 21 2007 - 11:20:18 CDT