Have Fun with ROWNUM

I have data :

SQL> select * from test;
ID FLAG
---------- ----------
1 0
2 0
3 1
4 0
5 0
6 1
7 0
8 0
9 0
10 0

10 rows selected.

I need to find id nearby 5 (id > 5 : 2 values, id < flag ="0:">

ID FLAG
---------- ----------
2 0
4 0
7 0
8 0

Let me show:

SQL> select * from test where flag = 0 order by id;

ID FLAG
---------- ----------
1 0
2 0
4 0
5 0
7 0
8 0
9 0
10 0

SQL> select z.* ,rownum r from (select * from test where flag = 0 order by id) z ;

ID FLAG R
---------- ---------- ----------
1 0 1
2 0 2
4 0 3
5 0 4
7 0 5
8 0 6
9 0 7
10 0 8

SQL> select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5 ;

R
----------
4

SQL> select y.id,y.flag from (select z.* ,rownum r from (select * from test where flag = 0 order by id) z) y where y.r < (select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5); 

ID FLAG 

---------- 

1 0 

2 0 

4 0 

SQL> select y.id,y.flag from (select z.* ,rownum r from (select * from test where flag = 0 order by id) z) y where y.r > (select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5);

ID FLAG
----------
7 0
8 0
9 0
10 0

SQL> select y.id,y.flag from (select z.* ,rownum r from (select * from test where flag = 0 order by id) z) y where y.r < (select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5) and y.r >= ((select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5) -2) or y.r > (select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5) and y.r <= ((select b.r from (select a.*,rownum r from (select * from test where flag = 0 order by id) a) b where b.id = 5) +2) ;

ID FLAG
---------- ----------
2 0
4 0
7 0
8 0

Note:
Anyway hard code make to decrease performace on DB.
If you often use a statment. You should use pl/sql.

Enjoy rownum!