title: Can the result of a subquery be joined with itself?
body: <p>Let's say I need to find the oldest animal in each zoo.  It's a typical maximum-of-a-group sort of query.  Only here's a complication: the zebras and giraffes are stored in separate tables.  To get a listing of all animals, be they giraffes or zebras, I can do this:</p>
  
  <pre><code>(SELECT id,zoo,age FROM zebras
   UNION ALL
   SELECT id,zoo,age FROM giraffes) t1
  </code></pre>
  
  <p>Then given <code>t1</code>, I could build a typical maximum-of-a-group query:</p>
  
  <pre><code>SELECT t1.*
  FROM t1
  JOIN
  (SELECT zoo,max(age) as max_age
   FROM t1
   GROUP BY zoo) t2
  ON (t1.zoo = t2.zoo)
  </code></pre>
  
  <p>Clearly I could store <code>t1</code> as a temporary table, but is there a way I could do this all within one query, and without having to repeat the definition of <code>t1</code>?  (Please let's not discuss modifications to the table design; I want to focus on the issue of working with the subquery result.)</p>