SHOW:
|
|
- or go back to the newest paste.
| 1 | SELECT | |
| 2 | g.id AS genre_id, | |
| 3 | g.name AS genre_name, | |
| 4 | - | t2.books_in_genre, |
| 4 | + | t2.books_in_genre AS books_in_genre, |
| 5 | b.id AS book_id, | |
| 6 | b.name AS book_name, | |
| 7 | b.date_added | |
| 8 | - | FROM book b |
| 8 | + | FROM genre g |
| 9 | - | RIGHT JOIN book_genre bg ON b.id = bg.book_id |
| 9 | + | OUTER JOIN ( |
| 10 | - | RIGHT JOIN genre g ON g.id = bg.genre_id |
| 10 | + | SELECT bg.genre_id AS genre_id, |
| 11 | - | JOIN ( |
| 11 | + | COUNT(b.id) as books_in_genre, |
| 12 | - | SELECT g.id genre_id, COUNT(b.id) books_in_genre, MAX(b.date_added) newest_book |
| 12 | + | FIRST_VALUE(b.id) OVER (PARTITION BY bg.genre_id ORDER BY b.date_added DESC, b.id DESC) AS newest_book_id |
| 13 | FROM book b | |
| 14 | - | RIGHT JOIN book_genre bg ON b.id = bg.book_id |
| 14 | + | LEFT JOIN book_genre bg ON b.id = bg.book_id |
| 15 | - | RIGHT JOIN genre g ON g.id = bg.genre_id |
| 15 | + | GROUP BY bg.genre_id |
| 16 | - | GROUP BY g.id |
| 16 | + | |
| 17 | ON g.id = t2.genre_id | |
| 18 | LEFT JOIN books b | |
| 19 | - | AND (b.date_added = t2.newest_book OR b.date_added IS NULL AND t2.newest_book IS NULL) |
| 19 | + | ON b.id = t2.newest_book_id |
| 20 | - | ORDER BY t2.books_in_genre DESC |
| 20 | + | ORDER BY books_in_genre DESC |
