6 Replies Latest reply on Aug 4, 2008 10:30 AM by Aketi Jyuuzou

# query only outer range

Hi,
I have a input table

INPUT
1
2
3
5
7
8
9
11
12
13
15
17
18
20
21
22
23

and i want only the following numbers:

OUPUT
1
3
5
7
9
11
13
15
17
18
20
23

As you see i want only the number from the outer range. Can this be done with without plsql?

Greetings
• ###### 1. Re: query only outer range
Hi,
``````SOTI@soti_9> WITH INPUT AS (
2    SELECT 1 AS Val FROM Dual UNION ALL
3    SELECT 2 FROM Dual UNION ALL
4    SELECT 3 FROM Dual UNION ALL
5    SELECT 5 FROM Dual UNION ALL
6    SELECT 7 FROM Dual UNION ALL
7    SELECT 8 FROM Dual UNION ALL
8    SELECT 9 FROM Dual UNION ALL
9    SELECT 11 FROM Dual UNION ALL
10    SELECT 12 FROM Dual UNION ALL
11    SELECT 13 FROM Dual UNION ALL
12    SELECT 15 FROM Dual UNION ALL
13    SELECT 17 FROM Dual UNION ALL
14    SELECT 18 FROM Dual UNION ALL
15    SELECT 20 FROM Dual UNION ALL
16    SELECT 21 FROM Dual UNION ALL
17    SELECT 22 FROM Dual UNION ALL
18    SELECT 23 FROM Dual
19  )
20  SELECT Val
21  FROM (
22      SELECT I.Val,
23        LAG(I.Val) OVER(ORDER BY I.Val) AS Prev_Val,
24        LEAD(I.Val) OVER(ORDER BY I.Val) AS Next_Val
25      FROM Input I
26    )
27  WHERE Prev_Val IS NULL
28    OR Val - Prev_Val <> 1
29    OR Next_Val IS NULL
30    OR Next_Val - Val <> 1
31  ORDER BY Val;

VAL
----------
1
3
5
7
9
11
13
15
17
18
20
23

12 rows selected.``````
Regards,
Dima
• ###### 2. Re: query only outer range
Thanks for fast response!
• ###### 3. Re: query only outer range
Prev_Val IS NULL
[...]
OR Next_Val IS NULL
You can avoid these tests on null, by adding something else into LAG and LEAD functions :
```select *
from (select val,
lead(val,1,val+2) over (order by val) val_next ,
lag(val,1,val-2) over (order by val) val_prev
from input)
where val!=val_next-1 or val!=val_prev+1;```
Nicolas.
• ###### 4. Re: query only outer range
what do you mean by outer range?

RD
• ###### 5. Re: query only outer range
```WITH INPUT AS (
SELECT 1 AS Val FROM Dual UNION ALL
SELECT 2 FROM Dual UNION ALL
SELECT 3 FROM Dual UNION ALL
SELECT 5 FROM Dual UNION ALL
SELECT 7 FROM Dual UNION ALL
SELECT 8 FROM Dual UNION ALL
SELECT 9 FROM Dual UNION ALL
SELECT 11 FROM Dual UNION ALL
SELECT 12 FROM Dual UNION ALL
SELECT 13 FROM Dual UNION ALL
SELECT 15 FROM Dual UNION ALL
SELECT 17 FROM Dual UNION ALL
SELECT 18 FROM Dual UNION ALL
SELECT 20 FROM Dual UNION ALL
SELECT 21 FROM Dual UNION ALL
SELECT 22 FROM Dual UNION ALL
SELECT 23 FROM Dual)
select Val
from (select Val,Lead(Val) over(order by Val) as LeadVal,
Lag(Val) over(order by Val) as LagVal
from INPUT)
where case when Val= all(LagVal+1,LeadVal-1)
then 1 else 0 end = 0;```
```VAL
---
1
3
5
7
9
11
13
15
17
18
20
23```
I have used is not true.

My old posts ;-)
Any Alternative SQL ?
What is the difference between "= NULL" and "IS NULL" in SQL?
• ###### 6. Re: query only outer range
OOPS
We can use more simple one.
```select Val
from (select Val,
count(*) over(order by Val
range between 1 preceding
and 1 following) as cnt
from INPUT)
where cnt < 3
order by Val;```