How to Efficiently Fetch the First N Rows Within Each Group in PostgreSQL?

PostgreSQL: Retrieving the Top N Rows Within Each Group

A frequent task in PostgreSQL involves selecting a set number of rows from each group. This can be efficiently achieved using a technique often called "Grouped LIMIT." Here's how to accomplish this using window functions (available in PostgreSQL 8.4 and later):

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

Explanation:

The ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) window function assigns a unique rank (rn) to each row within each section_id group, ordered by the name column. The outer query then filters these results, keeping only rows where the rank (rn) is less than or equal to N (replace N with your desired number of rows per group). This effectively limits the results to the top N rows for each group.

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