469,603 Members | 2,135 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

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.
Jun 27 '06 #1
2 1684
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
Jun 28 '06 #2
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:
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
Jul 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ariel Jakobovits | last post: by
5 posts views Thread by David Garamond | last post: by
8 posts views Thread by jefftyzzer | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.