How to get the first and the last value in a column per each sequence with the same value

Question

I would like to get the first and the last value in the column Status1 of each sequence of the same value!

This is an example of my table:

time                     Status1   Eficiencia   Lote
----------------------------------------------------
2020 06 14 18:03:48.457    70         80         95
2020 06 14 18:04:47.693    70         80         95
2020 06 14 18:06:58.203    55         80         95
2020 06 14 18:08:19.900    55         80         95
2020 06 14 18:09:45.537    55         80         95
2020 06 14 18:10:06.670   100         80         13
2020 06 14 18:10:27.297   100         80         13
2020 06 14 18:10:31.810   100         80         13
2020 06 14 18:10:43.187   100         80         13
2020 06 14 18:11:30.303    55         80         14
2020 06 14 18:12:07.563    55         80         14
2020 06 14 18:18:54.997    55         80         14

I tried this but using this I didn't get by each sequence but in the hole table!

;with cte as
(
    select
        *,
        RnAsc = row_number() over (partition by [Status1] order by time),
        RnDesc = row_number() over (partition by [Status1] order by time desc) 
    from 
        [VALORES_DB].[dbo].[OEE_TESTE]
)
select time, [Status1], Eficiencia, Lote, Status1 
from cte
where RnAsc = 1 or RnDesc = 1

I would like to get the follow:

time                      Status1   Eficiencia   Lote
------------------------------------------------------
2020 06 14 18:03:48.457     70          80        95
2020 06 14 18:04:47.693     70          80        95
2020 06 14 18:06:58.203     55          80        95
2020 06 14 18:09:45.537     55          80        95
2020 06 14 18:10:06.670    100          80        13
2020 06 14 18:10:43.187    100          80        13
2020 06 14 18:11:30.303     55          80        14
2020 06 14 18:18:54.997     55          80        14

score 1 · Answer 1 · answered Jun 21 '20 at 17:18

I would suggest lag() and lead():

select t.*
from (select t.*,
             lag(status1) over (order by time) as prev_status1,
             lead(status1) over (order by time) as next_status1
      from t
     ) t
where (prev_status1 is null or prev_status1 <> status1) or
      (next_status1 is null or next_status1 <> status1);

These comparisons determine where the value changes -- which is really what you are asking for.

How to get the first and the last value in a column per each sequence with the same value

1 Answers1