Daily Oracle

short Oracle take-away tips that you can put to use in under an hour.

Monday, July 10, 2006

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

http://techrepublic.com.com/5110-9592-6088140.html

Links

1 Comments:

  • At 8:25 PM, Blogger Chameeya said…

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

     

Post a Comment

<< Home