Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


CREATE TABLE event_order(
	event_id 	VARCHAR(30),	
  	state 		VARCHAR(30),
  	amount 		INT,
  	address		VARCHAR(30),
  	notes		VARCHAR(30),
  	timestamp	TIMESTAMP
);

INSERT INTO event_order VALUES
('order123', 'fulfilled', NULL, NULL, NULL, '2022-07-01T17:08:12.032316+00:00'),
('order123', NULL, NULL, NULL, 'Delivered to customer', '2022-07-01T17:07:12.032316+00:00'),
('order123', NULL , NULL, '300 Post St, CA 94108', NULL, '2022-07-01T17:06:12.032316+00:00'),
('order123', 'accepted', NULL, NULL, NULL,	'2022-07-01T17:05:12.032316+00:00'),
('order123', 'pending' , 100, NULL, NULL, '2022-07-01T17:04:12.032316+00:00');

CREATE TABLE



INSERT 0 5


select
    distinct event_id,
    first_value(state) over ( partition by event_id order  by case when state is not null then 0 else 1 end ASC, timestamp desc ),
   first_value(amount) over ( partition by event_id order  by case when amount is not null then 0 else 1 end ASC, timestamp desc),
   first_value(address) over ( partition by event_id order  by case when address is not null then 0 else 1 end ASC, timestamp desc),
   first_value(notes) over ( partition by event_id order  by case when notes is not null then 0 else 1 end ASC, timestamp desc),
   first_value(timestamp) over ( partition by event_id order  by case when timestamp is not null then 0 else 1 end ASC, timestamp desc)
from event_order;


event_id
first_value
first_value
first_value
first_value
first_value


order123
fulfilled
100
300 Post St, CA 94108
Delivered to customer
2022-07-01 17:08:12.032316



SELECT 1