How to Retrieve the Top N Rows per Group in PostgreSQL?

PostgreSQL: Retrieving the Top N Rows for Each Group

PostgreSQL often requires retrieving a specific number of rows from each group within a dataset. This is particularly useful when dealing with ranked data or needing to limit results per category. For example, you might want the top 3 products from each department, ordered by sales.

Illustrative Example:

Consider a table named products with the following structure and sample data:

product_id	department_id	product_name	sales
1	1	Product A	100
2	1	Product B	150
3	1	Product C	200
4	1	Product D	250
5	2	Product E	50
6	2	Product F	100
7	3	Product G	120
8	2	Product H	180

The objective is to retrieve the top 2 products (based on sales) from each department_id. The expected result would be:

product_id	department_id	product_name	sales
4	1	Product D	250
3	1	Product C	200
8	2	Product H	180
6	2	Product F	100
7	3	Product G	120

Solution using ROW_NUMBER() (PostgreSQL 8.4 and later):

PostgreSQL 8.4 and above offer the ROW_NUMBER() window function, providing an efficient solution. The following query accomplishes the task:

SELECT product_id, department_id, product_name, sales
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY sales DESC) AS rn,
    product_id, department_id, product_name, sales
  FROM
    products
) ranked_products
WHERE rn <= 2;

This query assigns a rank (rn) to each product within its department based on sales, then filters to include only those with a rank less than or equal to 2.

The above is the detailed content of How to Retrieve the Top N Rows per Group in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!