3

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:

(SELECT id,zoo,age FROM zebras
 UNION ALL
 SELECT id,zoo,age FROM giraffes) t1

Then given t1, I could build a typical maximum-of-a-group query:

SELECT t1.*
FROM t1
JOIN
(SELECT zoo,max(age) as max_age
 FROM t1
 GROUP BY zoo) t2
ON (t1.zoo = t2.zoo)

Clearly I could store t1 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 t1? (Please let's not discuss modifications to the table design; I want to focus on the issue of working with the subquery result.)

gcbenison
  • 11,723
  • 4
  • 44
  • 82
  • Your query is weird, it's the same as doing SELECT t1.* FROM t1 since t2 will contain all possible zoos of t1, you join the zoo of t1 with t1 itself... – Sebas May 28 '12 at 01:44

2 Answers2

2

Here is a link to the with clause.

Understanding the WITH Clause

with t1 as
(select id, zoo, age from zebras
union all
select id, zoo, age from giraffes)
select t1.*
from t1
join
(SELECT zoo,max(age) as max_age
FROM t1
GROUP BY zoo) t2
on (t1.zoo = t2.zoo);

Note: You could move t2 up to your with clause as well.

Note 2: An alternative solution is to simply create t1 as a view and use it in your query instead.

Nick
  • 2,524
  • 17
  • 25
  • i think this is not really sql standard compliant edit: ups, yes it is :-) Was not aware of the 1999 one... sry! – Sebas May 28 '12 at 01:45
  • I do think `WITH` is the way to go. However it appears MySQL (the RDBMS I use most often) [may not support it](http://stackoverflow.com/questions/324935/mysql-with-clause)? Frowny face if that is still true. – gcbenison May 28 '12 at 04:36
0

To find the oldest animal, you should use wjndoq functions:

select z.*
from (select z.*,
           row_number() over (partition by zoo order by age desc) as seqnum
      from ( <subquery to union all animals>) z
     )
where seqnum = 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786