And the post_comment child table contains these records:
| 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 Derived Table or Inline View
Let’s assume we want to get the first two most-commented posts that start with a given prefix (e.g., SQL) along with all their associated comments.
While the SQL standard provides the FETCH FIRST N ROWS ONLY clause to write a Top-N SQL query, that would not help us implement our use case because we need the first 2 post rows along with all their associated post_comment child records, no matter the size of the joined result set.
In order to fetch the expected result set, we would have to:
Filter the post records by the SQL prefix
JOIN the post and post_comment records and use the COUNT Window Function to check how many comments are associated with each post record
Rank the posts by their number of comments using the DENSE_RANK Window Function
Limit the result set to the first two most-commented posts
The SQL Derived Table Or Inline View query looks as follows:
DENSE_RANK() OVER (ORDER BY p_pc.comment_count DESC) AS ranking
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%'
WHERE p_pc_r.ranking <= 2
ORDER BY post_id, comment_id
And the result set of the SQL Derived Table or Inline View query looks as follows:
The inner-most subquery, p_pc, builds the JOIN between the post and post_comment tables and filters the post records by the provided prefix. To calculate the number of comments for each post, we don’t want to use GROUP BY as that would reduce the result set to a single record per aggregation partition. Therefore, we will use the COUNT Window Function instead.
The second subquery is p_pc_r, and its FROM clause is the result set of the previous p_pc subquery. The goal of the p_pc_r subquery is to rank the posts by the number of associated comments, and, for this purpose, we use the DENSE_RANK Window Function.
The outer-most query filters the p_pc_r query result set and extracts the top 2 most-commented posts along with their associated comments.
The main advantage of a Derived Table or Inline View is that it allows us to override the default SQL operation order, which is:
OVER Window Functions
If you take a look at the default SQL operation order, you will see why the outer-most query WHERE clause cannot be pushed to the p_pc_r query because, if we did so, the SQL query would fail to compile since the WHERE clause would reference the DENSE_RANK Window Function column that hasn’t got the chance to be calculated.
If you’re using ORACLE, the legacy Top-N query uses a Derived Table to calculate the ROWCOUNT