0

I am building a library database. I want to write a query that returns the top 5 books for each category, which means that it has to return the 5 books that are borrowed the most times for each one of the categories and the number of times they are borrowed.

The query involves the following tables:

Book (ISBN, title,pubYear,numpages, pubName)
borrows (memberID, ISBN, copyNr, date_of_borrowing, date_of_return)
belongs_to (ISBN, categoryName)

My approach is the following:

SELECT *
FROM(SELECT book.title, count(bo.ISBN) as Number_of_times_book_is_taken ,be.categoryName
     FROM belong_to as be INNER JOIN borrows as bo ON be.ISBN = bo.ISBN INNER JOIN book ON bo.ISBN = book.ISBN 
     GROUP BY bo.ISBN
     ORDER BY count(bo.ISBN) DESC) AS Popular
     ORDER BY categoryName, Number_of_times_book_is_taken DESC

I consider this works fine if I wanted to return the titles of the books, the number of times the books are taken and the category in which they belong.

However, I want to restrict the resutls so that I get only 5 books for each of the categories. If I use LIMIT 5 I will limit the whole result, which is not what I want. I am trying to limit each one of the categoryNames to appear max 5 times.

MJ13
  • 195
  • 6
  • What version of MySQL are you using? Or more directly, if you are building a new system, you should be using the latest version of MySQL, which supports the queries in both answers. – Gordon Linoff May 23 '19 at 11:13
  • @GordonLinoff: I build by database on localhost phpadmin and the version of MySql is 5.7... Is it possible to make it work in this version somehow? – MJ13 May 23 '19 at 19:33
  • . . Upgrade to 8+ and use one of the answers below. – Gordon Linoff May 24 '19 at 02:04
  • @GordonLinoff How do I upgrade the SQL version on phpmyadmin? – MJ13 May 24 '19 at 18:31
  • . . https://dev.mysql.com/doc/refman/8.0/en/upgrading.html. – Gordon Linoff May 25 '19 at 01:14
  • @GordonLinoff I downloaded the latest version of MySQL with MySQL workbench...But how do I run the newest version on phpmyadmin? – MJ13 May 25 '19 at 08:15
  • @GordonLinoff Could you provide an alterantive answer that is compatible with my older MySQL version? – MJ13 May 25 '19 at 12:07

2 Answers2

0

in mysql 8.0+ version you can use row_number()

with cte as
(
      SELECT book.title, count(bo.ISBN) as Number_of_times_book_is_taken ,
      be.categoryName,
      row_number() over(partition by be.categoryName order by count(bo.ISBN) desc) rn
     FROM belong_to as be INNER JOIN borrows as bo
     ON be.ISBN = bo.ISBN INNER JOIN book ON bo.ISBN = book.ISBN 
     GROUP BY be.categoryName,book.title

) select * from cte where rn<=5
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • When I try to run this SQL query I get the following error: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte as ( SELECT book.title, count(bo.ISBN) as Number_of_times_book_is_ta' at line 1` I am building the database on phpmyadmin. – MJ13 May 23 '19 at 09:30
  • @MJ13 check your mysql version it will work for >=8 version – Zaynul Abadin Tuhin May 23 '19 at 09:31
  • I build by database on localhost phpadmin and the version of MySql is 5.7... Is it possible to make it work in this version somehow? – MJ13 May 23 '19 at 19:33
0

Use the function Row Number, Partition by Category. Then select * from (Query) as Test Where Rownumber <= 5

See below link.

How to return a incremental group number per group in SQL

AgentShwang
  • 23
  • 5
  • I know you're only responding to the other Comment but if you just use a simple nest then it is standard. – AgentShwang May 24 '19 at 08:13
  • Thanks for your response and sorry for not responding to you. My problem is that I am building the databse on phpmyadmin and the MYSQL version installed doesn't support this query since I get an error. Is there a way to fix that? – MJ13 May 24 '19 at 18:31