Search code examples
mysqlsqlnestedsubquery

max() value from a nested select without ORDER BY


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)

Solution

  • 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
    ;