Databases
StackOverflow2013
Postsdbo
POMySQL: selecting max of a tuple
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: selecting max of a tuple
    text
    copied!<p>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). <strong>Edit: I've created a <a href="http://sqlfiddle.com/#!2/6acb7/9/2" rel="nofollow noreferrer">fiddle</a> with some sample data and an example query.</strong></p> <p>widget:</p> <pre><code>id int name varchar(50) </code></pre> <p>event:</p> <pre><code>id int time datetime </code></pre> <p>widget_event</p> <pre><code>widget_id int event_id int prop1 decimal(8,4) NULL prop2 int NULL ... propN typeN NULL </code></pre> <p>I want to select the most recent non-null value for each property, with roughly the following query:</p> <pre><code>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 </code></pre> <p>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 <a href="https://stackoverflow.com/questions/8748986/get-records-with-highest-smallest-whatever-per-group/8749095#8749095">the answer to "Get records with highest/smallest per group"</a>):</p> <pre><code>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) &gt; (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) &gt; (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 </code></pre> <p><strong>Is there a simpler way to write this query?</strong> 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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload