Search code examples
databaseormrelationships

Sorting by relationship count with mapper table

Say you have a books table, a students table and a mapper table, showing the many to many relationships between the books and the table. Then say you need to sort the books by most borrowed to least borrowed. What would be the most efficient way to accomplish this?

Solution

  • If for each borrowing of a book there is a record on Mapper table with the ID of that book, then following query will sort the book IDs by the number of times they are borrowed:

    SELECT BOOK_ID, COUNT(*) CNT 
FROM MAPPER 
GROUP BY BOOK_ID 
ORDER BY CNT DESC