CODEWITHSUNDEEP
mysqlsqlaggregate-functions
PFranchise
PFranchise

Reputation: 6752

Seemingly simple MYSQL query

Here is what my tables look like:

CREATE TABLE Author(
authorID INT PRIMARY KEY,
name VARCHAR(30)
);

CREATE TABLE book(
ISBN INT PRIMARY KEY,
title VARCHAR(30),
authorID INT,
inventory INT,
paperBack BOOLEAN,
fiction BOOLEAN,
FOREIGN KEY (authorID) REFERENCES Author(authorID)
);

I need to find out which author has written the most books.
I am working with a mixture of the following. I guess I am having trouble piecing it all together...

  SELECT authorID, count(*) 
    from book 
group by authorID;

I am not sure how to get the single row that has the highest count and then only get the authorID of that row. Once I have that authorID, I know how to get the name.

Upvotes: 0

Views: 77

Answers (3)

Lee
Lee

Reputation: 13542

If all you want is the ID of the author, then use ORDER BY and LIMIT 1 as others have pointed out. However, if you're going to want other fields from Author, or if you are interested in multiple authors (second highest count, lowest count, etc), then you should consider joining to a derived table, like this:

SELECT Author.*, d1.book_count
FROM Author
  JOIN (SELECT authorID, count(authorID) as book_count FROM book GROUP BY authorID) d1
  ON Author.authorID = d1.authorID
ORDER BY 
  d1.book_count

Would give a result set like this:

 +----------------------------------+
 | AuthorID  |  Name  |  book_count |
 +----------------------------------+
 | 1         | bob    | 20          |
 | 9001      | sam    | 18          |

 ...

Upvotes: 1

Sparky
Sparky

Reputation: 15105

Try this:

select a.name,count(*)
from author a
join book b on b.authorID=a.authorID
group by a.Name
order by 2 desc
limit 1

Upvotes: 2

Brian Glaz
Brian Glaz

Reputation: 15686

You don't need to use a subquery, you can just do something like this:

SELECT authorID FROM book GROUP BY authorID ORDER BY COUNT(*) DESC LIMIT 1

this should give you the authorID of the author with the most books.

Upvotes: 1

Related Questions