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 AS books_in_genre, | |
| 5 | b.id AS book_id, | |
| 6 | b.name AS book_name, | |
| 7 | b.date_added | |
| 8 | FROM genre g | |
| 9 | OUTER JOIN ( | |
| 10 | SELECT bg.genre_id AS genre_id, | |
| 11 | COUNT(b.id) as books_in_genre, | |
| 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 | LEFT JOIN book_genre bg ON b.id = bg.book_id | |
| 15 | GROUP BY bg.genre_id | |
| 16 | ) t2 | |
| 17 | ON g.id = t2.genre_id | |
| 18 | LEFT JOIN books b | |
| 19 | ON b.id = t2.newest_book_id | |
| 20 | ORDER BY books_in_genre DESC |
