### find gap in sequence with analytical function

SQL> WITH aquery AS

2 (SELECT measurement_id after_gap,

3 LAG(measurement_id,1,0) OVER (ORDER BY measurement_id) before_gap

4 FROM sensor_data)

5 SELECT

6 before_gap, after_gap

7 FROM

8 aquery

9 WHERE

10 before_gap != 0

11 AND

12 after_gap - before_gap > 1

13 ORDER BY

14 before_gap;

BEFORE_GAP AFTER_GAP

---------- ----------

2 4

5 7

## 1 Comments:

At 8:25 PM, Chameeya said…

Thanks, this helped me with a insert problem. I needed to find the first gap in a sequence.

Post a Comment

<< Home