Search code examples
sqloracle-databasemaxminrow-number

How to get change points in oracle select query?

How can I select change points from this data set 

1   0
2   0
3   0
4   100
5   100
6   100
7   100
8   0
9   0
10  0
11  100
12  100
13  0
14  0
15  0

I want this result 

4  7  100 
11 12 100
Solution

  • This query based on analytic functions lag() and lead() gives expected output:

    select id, nid, point 
  from (
    select id, point, p1, lead(id) over (order by id) nid 
      from (
        select id, point,
            decode(lag(point) over (order by id), point, 0, 1) p1,
            decode(lead(point) over (order by id), point, 0, 2) p2
         from test)
     where p1<>0 or p2<>0)
 where p1=1 and point<>0

    SQLFiddle

    Edit: You may want to change line 3 in case there only one row for changing point:

        ...
    select id, point, p1,
        case when p1=1 and p2=2 then id else lead(id) over (order by id) end nid 
    ...