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
Leave a Reply