How can I remove rows that break periodicity with a query?

Question

I have a table as below. There is not periodicity between data so i couldn't use functions like row_number() or rank.

**Column1**  |  **Column2**  |  **Timestamp**
Station1     |  Data1        | Date1    
Station1     |  Data2        | Date2
Station1     |  Data1        | Date3
Station1     |  Data2        | Date4
Station1     |  **Data3**    | Date5
Station1     |  Data2        | Date6
Station2     |  Data1        | Date7
Station2     |  Data2        | Date8
Station2     |  **Data3**    | Date9
Station2     |  Data2        | Date10

Normal data in column2 was like Data1 and Data2, but when it inserted Data3 to column2, it broke the periodicity between Data1 and Data2. I don't want to see data retrieving in my query from after Data3. I want it to be like below.

**Column1**  |  **Column2**  |  **Timestamp**
Station1     |  Data1        | Date1    
Station1     |  Data2        | Date2
Station1     |  Data1        | Date3
Station1     |  Data2        | Date4
Station2     |  Data1        | Date7
Station2     |  Data2        | Date8

What can I do to reach to this result? Thanks in advance.

Answer 1

You seem to want "data1" only if the next row is "data2" and "data2" only if the previous row is "data1". So:

select t.*
from (select t.*,
             lag(column2) over (partition by column1 order by timestamp) as prev_column2,
             lead(column2) over (partition by column1 order by timestamp) as next_column2
      from t
     ) t
where (column2 = 'data1' and next_column2 = 'data2') or
      (column2 = 'data1' and prev_column2 = 'data1')