StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's an alternative that requires only one instance of the log table:</p> <pre><code>SELECT d.id, d.name, SUBSTRING_INDEX( GROUP_CONCAT( l.message SEPARATOR '~' ORDER BY l.when DESC ) , '~' , 1 ) FROM device d LEFT JOIN log l ON d.id = l.device_id WHERE d.active = 1 GROUP BY d.id </code></pre> <p>This query finds the last log message by creating a tilde-separated list of messages, sorted by the date in descending order. That's done by the <code>GROUP_CONCAT</code>. The <code>SUBSTRING_INDEX</code> chips of the first entry of that list. </p> <p>There are 2 drawbacks to this approach:</p> <ul> <li><p>it uses <code>GROUP_CONCAT</code>. If the result of that function becomes too long, the result is truncated. You can remedy that if you do </p> <p><code>SET @@group_concat_max_len = @@max_allowed_packet;</code></p></li> </ul> <p>before running the query. You can do even better than that: since you're only interested in getting only one message, you can set <code>group_concat_max_len</code> to be as large as the maximum character length of the <code>message</code> column. This will save considerable memory as compared to using <code>@@max_alowed_packet</code>.</p> <ul> <li>it relies on a special separator (in the example, it's tilde (<code>'~'</code>)) that must not appear within the message text. You can change this to any separator string you like as long as you're sure it doesn't appear inside the message text.</li> </ul> <p>If you can live with these limitations, then this query is probably the fastest.</p> <p>Here are more alternatives that are about as complex as yours but may perform better.</p> <pre><code>SELECT d.id , d.name , l.message FROM ( SELECT d.id, d.name, MAX(l.when) lmax FROM device d LEFT JOIN log l ON d.id = l.device_id WHERE d.active = 1 GROUP BY d.id ) d LEFT JOIN log l ON d.id = l.device_id AND d.lmax = l.when ORDER BY d.id ASC; </code></pre> <p>another alternative:</p> <pre><code>SELECT d.id , d.name , l2.message FROM device d LEFT JOIN ( SELECT l.device_id , MAX(l.when) lmax FROM log l GROUP BY l.device_id ) l1 ON d.id = l1.device_id LEFT JOIN log l2 ON l1.device_id = l2.device_id AND l1.lmax = l2.when WHERE d.active = 1 ORDER BY d.id ASC; </code></pre>

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