SQL: Get the latest value from all rows but ignore nulls-CodePudding

I'm inserting a new row per order update so I can keep track of the state updates. Like this.

event_id	state	amount	timestamp
order123	accepted	NULL	2022-07-01T17:05:35.032316 00:00
order123	pending	100	2022-07-01T17:04:12.032316 00:00

How would I write a query that pulls out the latest value from each of those columns, ignoring the NULLs. This is what I'm looking for.

event_id	state	amount	timestamp
order123	accepted	100	2022-07-01T17:05:35.032316 00:00

This is as far as I've gotten, which gives me the latest record:

SELECT DISTINCT ON (event_id)
    event_id, timestamp, state, amount
    FROM event_order
ORDER BY event_id, timestamp DESC;

I came across this post which mentions LAST_VALUE but the solution seems like it only works with integers.

Any help will be appreciated.

CodePudding user response：

Try aggregating with a GROUP BY clause. This will work on the state because the minimum between "accepted" and "pending" is the former. At the same time, the MAX aggregation function will skip NULL values completely.

SELECT event_id, 
       MIN(state), 
       MAX(amount),
       MAX(timestamp)
FROM event_order
GROUP BY event_id

Check the demo here.

CodePudding user response：

CREATE temp TABLE test_101 (
    event_id text,
    state text,
    amount numeric,
    timestamp timestamptz
);

INSERT INTO test_101
    VALUES ('order123', 'accepted', NULL, '2022-07-01T17:05:35.032316 00:00'),
    ('order123', 'pending', 100, '2022-07-01T17:04:12.032316 00:00'),
    ('order123', 'pending', 101, '2022-07-01T17:04:13.032316 00:00'),
    ('order123', 'pending', 81, '2022-07-01T16:34:12.032316 00:00');

SELECT DISTINCT ON (event_id)
    event_id,
    state,
    amount,
    timestamp
FROM
    test_101
ORDER BY
    event_id,
    (amount IS NOT NULL IS FALSE),
    timestamp DESC;

credit: PostgreSQL: order by column, with specific NON-NULL value LAST