SQLize Online / PHPize Online  /  SQLtest Online

create table readers ( id serial primary key, name varchar ); create table books ( id serial primary key, title varchar, author varchar ); create table journals ( id serial primary key, reader_id int references readers(id) ON UPDATE CASCADE ON DELETE CASCADE, book_id int references books(id) ON UPDATE CASCADE ON DELETE CASCADE ); insert into readers(name) values ('josh'), ('carl'); insert into books(title, author) values ('0', '0'), ('1', '1'), ('2', '2'), ('3', '3'), ('4', '4'), ('5', '5'), ('6', '6'), ('7', '7'), ('8', '8'), ('9', '9'), ('10', '10'), ('11', '11'), ('12', '12'), ('13', '14'), ('14', '14'); insert into journals(reader_id, book_id) values (1, 1), (1, 1), (1, 1), (1, 1), (1, 2), (1, 2), (1, 2), (1, 2), (1, 3), (1, 3), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 10), (1, 10); select books.*, top_books.count from books, ( select books.id, count(journals) as count from books left join journals on journals.book_id = books.id group by books.id ) as top_books where books.id in (top_books.id) order by top_books.count desc limit 10;