MySQL: Select MAX() from sub-query with COUNT()
Solution 1
Instead of MAX()
you can simply use LIMIT
for the same. Also use JOIN
instead.
SELECT book_count,a.author_id,a.fn, a.ln
FROM author a
JOIN
(
SELECT c.author_id,COUNT(*) book_count FROM book c
GROUP BY c.author_id
) b
ON a.author_id = b.author_id
ORDER BY book_count DESC LIMIT 1
Output:
| BOOK_COUNT | AUTHOR_ID | FN | LN |
-------------------------------------------
| 2 | 12 | name1 | lname1 |
See this SQLFiddle
Edit:
If you want to use MAX()
for that, you have to use sub-query like this:
SELECT book_count,a.author_id,a.fn, a.ln
FROM author a
JOIN
(
SELECT c.author_id,COUNT(*) book_count FROM book c
GROUP BY c.author_id
) b
ON a.author_id = b.author_id
WHERE book_count =
(SELECT MAX(book_count)
FROM
(
SELECT c.author_id,COUNT(*) book_count FROM book c
GROUP BY c.author_id
) b )
See this SQLFiddle
Edit2:
Instead of using LIMIT
in outer query you can simply use it in inner query too:
SELECT book_count,a.author_id,a.fn, a.ln
FROM author a
JOIN
(
SELECT c.author_id,COUNT(*) book_count FROM book c
GROUP BY c.author_id
ORDER BY COUNT(*) DESC LIMIT 1
) b
ON a.author_id = b.author_id
See this SQLFiddle
Solution 2
In fact, MySQL has a lack of support SQL's standard, because it allows use aggregate functions w/o GROUP BY clause and returns random data in result. You should avoid the usage of aggregates in that way.
EDIT: I mean, for example in MySQL you can execute query like this:
SELECT
MAX(a), b, c
FROM
table
GROUP BY
b;
Which returns random data in c column, and that's terribly wrong.
Mark
Studied at the Polytechnic University of the Philippines as DOST-SEI Scholar Worked as Science Research Specialist I at Philippine Nuclear Research Institute (2012) Worked as Application Developer at ABS-CBN Broadcasting Corporation (2015) Currently working as Systems Analyst in United Laboratories, Inc (UNILAB) Competent in C# Programming (WPF, ASP.NET and Win Forms), PHP and MySQL, and C/C++ Programming Good in using Oracle Database and Microsoft SQL Server Reporting Services (BIDS-SSRS) Knowledgeable in Linux Operating System, Network Management, and Database Administration and Design For me: Programming is like sex: one mistake and you’re providing support for a lifetime. Favorite post: Hidden Features of C#?
Updated on January 11, 2020Comments
-
Mark over 4 years
Before you mark this as duplicate please take a look at this SQLFiddle.
I have this schema:
CREATE TABLE book(book_id int, book_name varchar(100), author_id int, editor_id varchar(100), isbn varchar(100)); INSERT INTO book VALUES (1 , 'Book1 Title' , 12 , 'Editor1' , '8000-9000' ), (2 , 'Book2 Title' , 98 , 'Editor1' , '8000-9001' ), (1 , 'Book1 Title' , 12 , 'Editor1' , '8000-9002' ), (3 , 'Book3 Title' , 3 , 'Editor1' , '8000-9003' ); CREATE TABLE author(author_id int, fn varchar(100), ln varchar(100)); INSERT INTO author VALUES (12, 'name1','lname1'), (98,'name2','lname2'), (3,'name3','lname3');
The sub-query:
SELECT c.author_id,COUNT(*) book_count FROM book c GROUP BY c.author_id
has a result:
| AUTHOR_ID | BOOK_COUNT | -------------------------- | 3 | 1 | | 12 | 2 | | 98 | 1 |
Now, the tricky part here is the result of this query:
SELECT MAX(book_count),a.* FROM author a,( SELECT c.author_id,COUNT(*) book_count FROM book c GROUP BY c.author_id ) b where a.author_id = b.author_id
is this:
| MAX(BOOK_COUNT) | AUTHOR_ID | FN | LN | ------------------------------------------------ | 2 | 3 | name3 | lname3 |
which should be like this:
| MAX(BOOK_COUNT) | AUTHOR_ID | FN | LN | ------------------------------------------------ | 2 | 12 | name1 | lname1 |
What do you think is wrong in the query?