How to Limit Rows Within Each Group in PostgreSQL?

PostgreSQL grouping limit: display the first N rows in each group

It is often necessary in databases to extract a limited number of rows from a specific grouping. In PostgreSQL, this requirement can be met using the LIMIT clause in conjunction with grouping operations.

The following example demonstrates the need to retrieve the first two rows (sorted by the name column) in each section_id grouping. For example, from the given table:

<code> id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H</code>

We want to achieve the following results:

<code> id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G</code>

In PostgreSQL versions prior to 8.4, there was a lack of built-in functions to handle such queries efficiently. However, in PostgreSQL 8.4 and later, a new solution has emerged:

<code class="language-sql">SELECT *
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r, t.*
  FROM xxx t
) x
WHERE x.r <= 2;</code>

This solution utilizes the ROW_NUMBER() window function to assign a sequence number to each row within each section_id partition. The ORDER BY clause specifies the sorting criteria (name in this case). A LIMIT 2 condition is then applied to the subquery, limiting the results to the first two rows of each grouping (sorted by name ).

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