Open simplenotezy opened 6 months ago
A workaround is to create a second cte and do the WHERE rank...
filtering there, and then select from this new CTE:
WITH cte AS (
SELECT
s.title,
sg.genre,
RANK() OVER (PARTITION BY sg.genre ORDER BY s.title) AS rank
FROM
show s
JOIN
show_genre sg ON s.id = sg.show_id
WHERE
sg.genre IN ('Action', 'Adventure')
),
filtered_cte AS (
SELECT
title,
genre
FROM
cte
WHERE
rank <= 10
)
SELECT * FROM filtered_cte
This is interesting, thanks. I actually haven't considered CTE yet but that's quite common in MySQL 8 and PG. Will take a look shortly with the other issues
Using Rank() OVER (PARTITION...) results in this error:
Full query: