StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs it possible to add conditions to a MAX() call in an aggregated query?
text
Body
copied!<h2>Background</h2> <p>My typical use case:</p> <pre><code># Table id category dataUID --------------------------- 0 A (NULL) 1 B (NULL) 2 C text1 3 C text1 4 D text2 5 D text3 # Query SELECT MAX(`id`) AS `id` FROM `table` GROUP BY `category` </code></pre> <p>This is fine; it will strip out any "duplicate categories" in the recordset that's being worked on, giving me the "highest" ID for each category.</p> <p>I can then go on use this ID to pull out all the data again:</p> <pre><code># Query SELECT * FROM `table` JOIN ( SELECT MAX(`id`) AS `id` FROM `table` GROUP BY `category` ) _ USING(`id`) # Result id category dataUID --------------------------- 0 A (NULL) 1 B (NULL) 3 C text1 5 D text3 </code></pre> <p>Note that this is <em>not</em> the same as:</p> <pre><code>SELECT MAX(`id`) AS `id`, `category`, `dataUID` FROM `table` GROUP BY `category` </code></pre> <p>Per <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html" rel="nofollow">the documentation</a>:</p> <blockquote> <p>In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:</p> <pre><code>SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers AS c WHERE o.custid = c.custid GROUP BY o.custid; </code></pre> <p>For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.</p> <p><strong>MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.</strong> This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. <strong>However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.</strong></p> <p>[..]</p> <p><strong>This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate.</strong></p> </blockquote> <p>So I'd get an unspecified value for <code>dataUID</code> — as an example, either <code>text2</code> <em>or</em> <code>text3</code> for result with <code>id</code> <code>5</code>.</p> <p>This is actually a problem for other fields in my real case; as it happens, for the <code>dataUID</code> column specifically, generally I don't really care <em>which</em> value I get.</p> <hr> <h2>Problem</h2> <p>However!</p> <p>If any of the rows for a given <code>category</code> has a <code>NULL</code> <code>dataUID</code>, and at least one other row has a non-<code>NULL</code> <code>dataUID</code>, I'd like <code>MAX</code> to ignore the <code>NULL</code> ones.</p> <p>So:</p> <pre><code>id category dataUID --------------------------- 4 D text2 5 D (NULL) </code></pre> <p>At present, since I pick out the row with the maximum ID, I get:</p> <pre><code>5 D (NULL) </code></pre> <p>But, because the <code>dataUID</code> is <code>NULL</code>, instead I want:</p> <pre><code>4 D text2 </code></pre> <p><strong>How can I get this? How can I add conditional logic to the use of aggregate <code>MAX</code>?</strong></p> <hr> <p>I thought of maybe handing <code>MAX</code> a tuple and pulling the <code>id</code> out from it afterwards:</p> <pre><code>GET_SECOND_PART_SOMEHOW(MAX((IF(`dataUID` NOT NULL, 1, 0), `id`))) AS `id` </code></pre> <p>But I don't think <code>MAX</code> will accept arbitrary expressions like that, let alone tuples, and I don't know how I'd retrieve the second part of the tuple after-the-fact.</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