By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (id int, datetime timestamp, state text, value_needed text);
INSERT INTO tbl VALUES
(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-12 09:42:41.319000', 'done' , 'E');
CREATE TABLE
INSERT 0 6
SELECT *
FROM (
SELECT *,
LEAD (state) OVER (ORDER BY datetime DESC)
IS DISTINCT FROM state AS first_in_group
FROM tbl
) t
WHERE first_in_group
ORDER BY datetime DESC
LIMIT 1
| id | datetime | state | value_needed | first_in_group |
|---|---|---|---|---|
| 5 | 2021-04-07 09:42:41.319 | done | D | t |
SELECT 1
SELECT *,
LEAD (state) OVER (ORDER BY datetime DESC)
IS DISTINCT FROM state AS first_in_group
FROM tbl
| id | datetime | state | value_needed | first_in_group |
|---|---|---|---|---|
| 6 | 2021-04-12 09:42:41.319 | done | E | f |
| 5 | 2021-04-07 09:42:41.319 | done | D | t |
| 4 | 2021-04-05 10:42:41.319 | incomplete | C | f |
| 3 | 2021-04-05 09:42:41.319 | incomplete | C | t |
| 2 | 2021-04-04 09:42:41.319 | done | B | t |
| 1 | 2021-04-01 09:42:41.319 | incomplete | A | t |
SELECT 6