lundi 5 octobre 2015

SQL query:Having number=max(number) doesn't work

I have two tables,Writer and Books. A writer can pruduce many books. I want to get the all writers who produce maximal number of books.

Firstly, my sql query is like:

     SELECT Name FROM(
        SELECT Writer.Name,COUNT(Book.ID) AS NUMBER FROM Writer,Book
        WHERE
            Writer.ID=Book.ID
        GROUP BY Writer.Name
        )
        WHERE NUMBER=(SELECT MAX(NUMBER) FROM
                      (SELECT Writer.Name,COUNT(Book.ID) AS NUMBER FROM Writer,Book
                      WHERE Writer.ID=Book.ID
                      GROUP BY Writer.Name
        )

It works. However I think this query is too long and there exists some duplications. I want to make this query shorter. So I try another query like this:

SELECT Name FROM(
        SELECT Writer.Name,COUNT(Book.ID) AS NUMBER FROM Writer,Book
        WHERE
            Writer.ID=Book.ID
        GROUP BY Writer.Name
        HAVING NUMBER = MAX(NUMBER)
        )

However, this HAVING clause doesn't work and my sqlite says its an error. I don't know why. Can anyone explain to me ? Thank you!

Aucun commentaire:

Enregistrer un commentaire