How to get first and last value by same item in sequence, with possibility of the same item occurring again Similar to this question but with the possibility that the same name can appear again in the following rows.

I have this table:

id name     value
1  Daniel   3
2  Daniel   7
3  Daniel   2
4  Carol    9
5  Carol    4
6  Carol    9
7  Ray      5
8  Charles  1
9  Daniel   6
10 Daniel   5
11 Daniel   5


I would like to get the first and the last value (ordered by id) in a table like:

name    value_1st  value_last
Daniel  3         2
Carol   9         9  
Ray     5         5
Charles 1         1
Daniel  6         5
WITH 
cte1 AS ( SELECT *, 
                 CASE WHEN name = LAG(name) OVER (ORDER BY id) 
                      THEN 0 
                      ELSE 1 
                      END the_same
          FROM test ),
cte2 AS ( SELECT *, SUM(the_same) OVER (ORDER BY id) group_num
          FROM cte1 )
SELECT DISTINCT
       name, 
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id) value_1st,
       FIRST_VALUE(value) OVER (PARTITION BY group_num ORDER BY id DESC) value_last 
FROM cte2;


fiddle