Earliest and latest values per id and variable
Suppose I have this changelog data:
| id | variable | change date | start value | end value |
|---|---|---|---|---|
| 1 | a | 2026-01-14 00:00:04 | 105 | 110 |
| 1 | a | 2026-01-14 00:00:02 | 100 | 105 |
| 1 | b | 2026-01-14 00:00:03 | 88 | 77 |
| 1 | b | 2026-01-14 00:00:02 | [null] | 88 |
| 2 | a | 2026-01-14 00:00:03 | 8 | 9 |
| 2 | a | 2026-01-14 00:00:02 | 7 | 8 |
| 2 | a | 2026-01-14 00:00:01 | 6 | 7 |
| 3 | a | 2026-01-14 00:00:05 | 45 | 55 |
Via postgresql, I want to capture the id, variable, latest change date, the start value from the earliest change and the end value of the latest change, per variable, per id. The query result would be:
| id | variable | change date | start value | end value |
|---|---|---|---|---|
| 1 | a | 2026-01-14 00:00:04 | 100 | 110 |
| 1 | b | 2026-01-14 00:00:03 | [null] | 77 |
| 2 | a | 2026-01-14 00:00:03 | 6 | 9 |
| 3 | a | 2026-01-14 00:00:05 | 45 | 55 |
How should I go about this?