Find the book with the most upvotes(like=1) per author

user_16432160 0
Viewed 43

Given these tables below

Authors Table

ID Name
1 Ron
2 John

Books Table

ID Title author_id
1 Book1 1
2 Book2 1
3 Book3 2

Votes Table

ID book_id Vote(like = 1, dislike = 0)
1 1 1
2 1 1
3 2 0

query i composed

SELECT
      name,
      b.title,
      MAX(upvote)
FROM
     (SELECT
             COUNT(IF(v.vote = 1, v.vote, NULL)) AS upvote,
             b.id AS book_id,
             b.author_id AS author_id
      FROM
           books b
           LEFT JOIN votes v ON b.id = v.book_id
      GROUP BY b.author_id, b.id
     )AS X
    LEFT JOIN books b ON x.book_id = b.id
     INNER JOIN authors a ON x.author_id = a.id
GROUP BY a.id

Problem: Find the book with the most upvotes(like=1) per author.

im stuck on this problem for 2 hours or so now, i still couldnt get the title of the MAX(vote). Based on the table above, in the main SELECT clause, it must return Book1 but instead I get Book2. Now how will I get the title of the MAX(vote)? I still couldn't figure out how to solve this one.

mysql sql
2 Answers

I have simulated your tables and result is below. I think you think very complex about this issue. If this is not what you are looking for please share a sample table what you want to see as a result so I will try to help you.

EDIT: Updated table and SQL query according to your new needs.

+----+-------+-----------+------+----------+------------+
| id | title | author_id | name | vote_sum | vote_count |
+----+-------+-----------+------+----------+------------+
|  1 | Book1 |     1     |  Ron |     2    |      2     |
+----+-------+-----------+------+----------+------------+
|  2 | Book2 |     1     |  Ron |     0    |      1     |
+----+-------+-----------+------+----------+------------+
|  3 | Book3 |     2     | John |   NULL   |      0     |
+----+-------+-----------+------+----------+------------+

SELECT 
    books.id,
    books.title,
    books.author_id,
    authors.name,
    SUM(votes.vote) as vote_sum,
    COUNT(votes.vote) as vote_count
FROM books
LEFT JOIN authors ON books.author_id = authors.id
LEFT JOIN votes ON books.id = votes.book_id
GROUP BY books.title, authors.id
ORDER BY vote_sum DESC
user_108672190

Probably the easiest aproach is to join and count first and then look at the authors' maximum upvotes in this intermediate result with an analytic function:

select name, title, upvotes
from
(
  select
    a.name,
    b.title,
    count(*) as upvotes,
    max(count(*)) over (partition by a.id) as max_upvotes_for_author
  from authors a
  join books b on b.author_id = a.id
  join votes v on v.book_id = b.id and v.vote = 1
  group by b.id
) counted
where upvotes = max_upvotes_for_author
order by name;

This is standard SQL. MAX OVER is featured since MySQL 8.

Your own query is invalid. In your main query you group by author, but select a book title. As an author can have written many books, you would have to tell the DBMS which book to select.

As is, the DBMS should raise an error. If it doesn't, then you are not working in ONLY_FULL_GROUP_BY mode. Unfortunately, MySQL allows a cheat mode instead and silently applies ANY_VALUE to all columns that are not dependent on the GROUP BY clause then. What is happening in your query hence is that you involuntarily select ANY_VALUE(b.title) and the DBMS just picks a book title per author arbitrarily.

Make sure to SET sql_mode = 'ONLY_FULL_GROUP_BY'; in MySQL in order to avoid writing invalid queries.

user_22707620
Related