title: Is it possible to add conditions to a MAX() call in an aggregated query?
body: <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> &mdash; 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>