How to use ROW_NUMBER() with a Common Table Expression (CTE) In SQL
Let’s see How to retrieve the top 2 rows per product_id from a sales table, ranked by amount in descending order.
WITH ranked_sales AS (
SELECT
id,
product_id,
amount,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) AS row_num
FROM
sales
)
-- Select top 2 rows per product_id
SELECT
id,
product_id,
amount
FROM
ranked_sales
WHERE
row_num <= 2; -- or N according to the user need
Output:
Explanation:
- In this method, a temporary result set called “ranked_sales” is created using a Common Table Expression (CTE).
- To assign a unique row number to each row within a group defined by the “product_id” column in the CTE, we use the ROW_NUMBER() window function, ordering by the “amount” column in descending order.
- Lastly, we retrieve the top two rows per “product_id” by choosing rows from the “ranked_sales” CTE where the row number is less than or equal to 2 or N.
How to Restrict Results to Top N Rows per Group in PostgreSQL?
In data analysis, understanding how to extract meaningful insights from grouped data is crucial. PostgreSQL, a powerful open–source relational database management system, provides robust features to handle such scenarios effectively.
One common analytical task is limiting results to the top N rows within each group, which can be invaluable for various analyses, such as tracking student performance, analyzing sales data, or evaluating product popularity.
In this article, we’ll explore three effective methods in PostgreSQL to restrict results to the top N rows per group, allowing you to make the most of your data analysis efforts.
Contact Us