I have a table of widgets and a table of events, and a junction table joining them. The junction table contains widget properties that might have changed during the event (if non-null). Edit: I've created a fiddle with some sample data and an example query.
widget:
id int
name varchar(50)
event:
id int
time datetime
widget_event
widget_id int
event_id int
prop1 decimal(8,4) NULL
prop2 int NULL
...
propN typeN NULL
I want to select the most recent non-null value for each property, with roughly the following query:
select
widget.id,
widget.name,
max((event1.time, event1.id, widget_event1.prop1)),
max((event2.time, event2.id, widget_event2.prop2)),
...
max((eventN.time, eventN.id, widget_eventN.propN)),
from
widget
left join (widget_event widget_event1, event event1)
on widget_event1.widget_id = widget.id
and widget_event1.event_id = event1.id
and widget_event1.prop1 is not null
left join (widget_event widget_event2, event event2)
on widget_event2.widget_id = widget.id
and widget_event2.event_id = event2.id
and widget_event2.prop2 is not null
...
group by
widget.id
As far as I can tell, using a tuple in the max expression is not legal, and the best I can come up with is this more complicated query (based on the answer to "Get records with highest/smallest per group"):
select
widget.id,
widget.name,
widget_event1.prop1,
widget_event2.prop2,
...
widget_eventN.propN
from
widget
left join (widget_event widget_event1, event event1)
on widget_event1.widget_id = widget.id
and widget_event1.event_id = event1.id
and widget_event1.prop1 is not null
left join (widget_event widget_event1_f, event event1_f)
on widget_event1_f.widget_id = widget.id
and widget_event1_f.event_id = event1_f.id
and widget_event1_f.prop1 is not null
and (event1_f.time, event1_f.id) > (event1.time, event1.id)
left join (widget_event widget_event2, event event2)
on widget_event2.widget_id = widget.id
and widget_event2.event_id = event2.id
and widget_event2.prop2 is not null
left join (widget_event widget_event2_f, event event2_f)
on widget_event2_f.widget_id = widget.id
and widget_event2_f.event_id = event2_f.id
and widget_event2_f.prop2 is not null
and (widget_event2_f.time, widget_event2_f.id) > (widget_event2.time, widget_event2.id)
...
where
widget_event1_f.prop1 is null
and widget_event2_f.prop2 is null
...
and widget_eventN_f.propN is null
group by
widget.id
Is there a simpler way to write this query? It seems strange that you can compare tuples but can't select the max of a tuple, so I'm hoping there's a simple bit of syntax that will reduce the amount of error-prone boilerplate.