Filtering sequential data

Question

I have the following query (example found here on this forum) that generates a sequence number to mark each change in Location.

WITH t(ID, col1  ,Location) AS (
select 1, 1 , 1 union all  
select 1, 2 , 1 union all  
select 1, 3 , 2 union all  
select 1, 4 , 2 union all  
select 1, 5 , 1 union all  
select 1, 6 , 2 union all  
select 1, 7 , 2 union all  
select 1, 8 , 3 union all 
select 2, 1 , 1 union all  
select 2, 2 , 2 union all  
select 2, 3 , 2 union all  
select 2, 4 , 2 union all  
select 2, 5 , 1 union all  
select 2, 6 , 1 union all  
select 2, 7 , 2 union all  
select 2, 8 , 3
)
SELECT t.ID, t.col1, t.Location,
    sum(x) OVER (partition by ID order by col1) sequence
FROM (
    SELECT t.*,
        CASE WHEN Location = lag(Location) OVER (order by ID, col1) THEN 0
            ELSE 1
        END x
    FROM t
    ) t
ORDER BY ID, col1
;

Now I would like to keep only those rows that indicate the sequential path through the different locations for each ID. How can I filter the data accordingly so that the following result is generated:

ID  Location
1   1
1   2
1   1
1   2
1   3
2   1
2   2
2   1
2   2
2   3

Is there a way to achieve his?

score 0 · Accepted Answer · answered Mar 19 '18 at 10:42

You seem to want to remove adjacent duplicates:

SELECT t.ID, t.col1, t.Location
FROM (SELECT t.*,
             (CASE WHEN Location = lag(Location) OVER (order by ID, col1) THEN 0
                   ELSE 1
              END) x
      FROM t
     ) t
WHERE x = 1
ORDER BY ID, col1;

I used the structure of your query. I would actually write this as:

SELECT t.ID, t.col1, t.Location
FROM (SELECT t.*,
            lag(Location) OVER (order by ID, col1) as prev_location
      FROM t
     ) t
WHERE prev_location is NULL or prev_location <> location
ORDER BY ID, col1;

This is exactly what I was searching for. Thanx very much. I appreciate your help! — Bertusian, Mar 20 '18 at 07:50

Filtering sequential data

1 Answers1