Aggregate functions

I took a lab exam today.
However, though the exam is over I'm still thinking about
one of the questions.

I had a schema for a bookstore database defined like this:

Book:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ISBN | varchar(30) | NO | PRI | NULL | |
| Title | varchar(50) | NO | | NULL | |
| PubDate | year(4) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+

Author:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Aname | varchar(50) | NO | PRI | NULL | |
| DOB | date | NO | | NULL | |
| Sex | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+

Wrote:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Aname | varchar(50) | NO | MUL | NULL | |
| ISBN | varchar(30) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+

Plus some other tables about the publishers, not relevant to
this question.

A book can have several authors who wrote the book together.

The question was:
"Find the book(s) with the largest number of authors."

After a while I came up with the following query which
seemed to work.

select Title
from Book
where ISBN=
(
select ISBN
from
(
select W.ISBN,
count(W.ISBN) as cnt
from Wrote W
group by W.ISBN
) as A
where A.cnt=
(
select max(B.cnt)
from
(
select count(W.ISBN) as cnt
from Wrote W
group by W.ISBN
) as B
)
);


But I find the query to by somewhat ugly so I was wondering
if there is a more elegant/simple way of achieving the same
result without defining views which was not allowed.
In particular it is bothering me that I count the number of
authors per book twice. If possible I would have liked to
limit the nesting depth as well.

Mikkel.

re: Aggregate functions

select title,xisbn from book
left outer join (select count(isbn) as xisbn from wrote group by xisbn) as
t1 on book.aname=t1.aname
order by xisbn desc

...translate that from MySQL, basically unnammed view or something

re: Aggregate functions

I guess I'm missing something embarrassingly obvious but do you even
need the derived table?

select w.ISBN, count(w.Aname) no_of_authors, b.title
from Wrote w
left join Book b on b.ISBN = w.isbn
group by w.ISBN order by no_of_authors DESC LIMIT 1;

Jared wrote: