this question I've found is driving me nuts, (edit:) because I'm trying to achieve this without the order by method. I have a table:
BookAuthors(book, author)
book author
------ ---------
1 Joy McBean
2 Marti McFly
2 Joahnn Strauss
2 Steven Spoilberg
1 Quentin Toronto
3 Dr E. Brown
both, book and author, are keys.
Now I would like to select the 'book' value with highest number of different 'author', and the number of authors. In our case, the query should retrieve the 'book' 2 with a number of 3 authors.
book authors
-------- ------------
2 3
I've been able to group them and obtain the number of authors for each book with this query:
select B.book, count(B.author) as authors
from BookAuthors B
group by B.book
which will results in:
book authors
-------- -------------
1 2
2 3
3 1
Now I would like to obtain only the book with the highest number of authors. This is one of the queries I've tried:
select Na.libro, Na.authors from (
select B.book, count(B.author) as authors
from BookAuthors B
group by B.book
) as Na
where Na.authors in (select max(authors) from Na)
and
select Na.libro, Na.authors from (
select B.book, count(B.author) as authors
from BookAuthors B
group by B.book
) as Na
having max( Na.authors)
I'm a little bit struggled...
Thank you for your help.
EDIT: since @Sebas was kind to reply right AND expanding my question, here is the solution come to my mind using the CREATE VIEW method:
create view auth as
select A.book, count(A.author)
from BooksAuthors A
group by A.book
;
and then
select B.book, B.nAuthors
from auth B
where B.nAuthors = (select max(nAuthors)
from auth)
SELECT cnt.book, maxauth.mx
FROM (
SELECT MAX(authors) as mx
FROM
(
SELECT book, COUNT(author) AS authors
FROM BookAuthors
GROUP BY book
) t
) maxauth JOIN
(
SELECT book, COUNT(author) AS authors
FROM BookAuthors
GROUP BY book
) cnt ON cnt.authors = maxauth.mx
This solution would be more beautiful and efficient with a view:
CREATE VIEW v_book_author_count AS
SELECT book, COUNT(author) AS authors
FROM BookAuthors
GROUP BY book
;
and then:
SELECT cnt.book, maxauth.mx
FROM (
SELECT MAX(authors) as mx
FROM v_book_author_count
) maxauth JOIN v_book_author_count AS cnt ON cnt.authors = maxauth.mx
;