PostgreSQL return last row in each sequence of same column values (alternation)

Question

We are using PostgreSQL 10.23 database. We have the following table of test results:

id	test_result	name
1	OK	test 1
2	OK	test 2
3	ERROR	test 3
4	ERROR	test 4
5	OK	test 5
6	OK	test 6
7	WARNING	test 7

We would like to return rows with the largest id for the sequence of the same consecutive test_result values. Our expected output is:

id	test_result	name
2	OK	test 2
4	ERROR	test 4
6	OK	test 6
7	WARNING	test 7

Answer 1

Use the window function lead():

select
    id, 
    test_result, 
    name
from (
    select 
        id, 
        test_result, 
        name,
        lead(test_result) over (order by id) as next_result
    from my_table
    ) s
where test_result is distinct from next_result;

Test it in db<>fiddle.