0

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.

Community
  • 1
  • 1
Sam
  • 1,260
  • 2
  • 11
  • 32

0 Answers0