Example 1
Show every ticket event with the final status for that ticket
The full-partition frame lets every row see the final row in its ticket partition, not just rows up to the current event.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE ticket_events (ticket_id INT, step_no INT, status VARCHAR(20));
INSERT INTO
ticket_events
VALUES
(1, 1, 'open'),
(1, 2, 'waiting'),
(1, 3, 'closed'),
(2, 1, 'open'),
(2, 2, 'escalated');Validated query Shared across supported engines.
SQL
SELECT
ticket_id,
step_no,
status,
LAST_VALUE (status) OVER (
PARTITION BY
ticket_id
ORDER BY
step_no ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS final_status
FROM
ticket_events
ORDER BY
ticket_id,
step_no;Expected result Returned rows for the shared example.
| ticket_id | step_no | status | final_status |
|---|---|---|---|
| 1 | 1 | open | closed |
| 1 | 2 | waiting | closed |
| 1 | 3 | closed | closed |
| 2 | 1 | open | escalated |
| 2 | 2 | escalated | escalated |
The explicit frame makes the result consistent across engines.