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
Links
Subscribe to this feed
posted by Siew Kam Onn @ 7:10 PM
1 comments
