And the post_comment child table contains the following data:
| id | review | post_id |
| 1 | SQL:2016 is great! | 1 |
| 2 | SQL:2016 is excellent! | 1 |
| 3 | SQL:2016 is awesome! | 1 |
| 4 | SQL:2011 is great! | 2 |
| 5 | SQL:2011 is excellent! | 2 |
| 6 | SQL:2008 is great! | 3 |
SQL CTE (Common Table Expression)
We now want to get the first two most-commented posts that start with the SQL prefix along with all their associated comments.
We cannot use a simple Top-N SQL query that defines a FETCH FIRST 2 ROWS ONLY clause because that would bring the first 2 records of the joined post and post_comment result set, and that’s not what we want.
In order to fetch the desired result set, we need to:
JOIN the post and post_comment and filter by the SQL prefix
Use a COUNT Window Function to determine how many comments are associated with each post
Use the DENSE_RANK Window Function to rank the posts by their number of comments
The CTE query uses the WITH clause to define an alias to a given subquery. For this reason, we can reference the subquery in a subsequent WITH subquery or in the outer query that builds the final result set.
Our CTE query looks as follows:
p_pc AS (
p.id AS post_id,
p.title AS post_title,
pc.id AS comment_id,
pc.review AS comment_review,
COUNT(post_id) OVER(PARTITION BY post_id) AS comment_count
FROM post p
LEFT JOIN post_comment pc ON p.id = pc.post_id
WHERE p.title LIKE 'SQL%'
p_pc_r AS (
DENSE_RANK() OVER (ORDER BY p_pc.comment_count DESC) AS ranking
WHERE p_pc_r.ranking <= 2
ORDER BY post_id, comment_id
The first subquery is p_pc and defines the JOIN and the WHERE clause that filters the post records. The p_pc query projection includes a COUNT Window Function that provides the number of comments for each post.
The second subquery is p_pc_r and, as you can see, it references the p_pc subquery we have previously defined. The goal of the p_pc_r subquery is to add a new column that ranks the posts by the number of associated comments. For this purpose, we have used the DENSE_RANK Window Function.
The last query filters the p_pc_r query result set and extracts just the top 2 most-commented posts along with their associated comments.
The reason why the WHERE clause in the final query cannot be moved to the p_pc_r query is that the WHERE clause evaluates before Window Functions. It’s the same argument we’ve been using for writing legacy Top-N queries using Oracle.