Select the first row in the last group of consecutive rows

Question

How would I select the row that is the first occurrence in the last 'grouping' of consecutive rows, where a grouping is defined by the consecutive appearance of a particular column value (in the example below state).

For example, given the following table:

id	datetime	state	value_needed
1	2021-04-01 09:42:41.319000	incomplete	A
2	2021-04-04 09:42:41.319000	done	B
3	2021-04-05 09:42:41.319000	incomplete	C
4	2021-04-05 10:42:41.319000	incomplete	C
5	2021-04-07 09:42:41.319000	done	D
6	2021-04-012 09:42:41.319000	done	E

I would want the row with id=5 as it it is the first occurrence of state=done in the last (i.e. most recent) grouping of state=done.

Answer 1

Assuming all columns NOT NULL.

SELECT *
FROM   tbl t1
WHERE  NOT EXISTS (
   SELECT FROM tbl t2
   WHERE  t2.state <> t1.state
   AND    t2.datetime > t1.datetime
   )
ORDER  BY datetime
LIMIT  1;

db<>fiddle here

NOT EXISTS is only true for the last group of peers. (There is no later row with a different state.) ORDER BY datetime and take the first. Voilá.