How to Select the Top N Rows within Each Group in PostgreSQL?

Using grouping LIMIT in PostgreSQL to select rows within a group

In PostgreSQL, you can use window functions and PARTITION BY clause to retrieve the first N rows of each grouping. This technique is particularly useful when the rows in each grouping need to be sorted by a specific column.

Consider the following form:

CREATE TABLE xxx (
  id SERIAL PRIMARY KEY,
  section_id INT,
  name VARCHAR(255)
);
INSERT INTO xxx (id, section_id, name) VALUES
  (1, 1, 'A'),
  (2, 1, 'B'),
  (3, 1, 'C'),
  (4, 1, 'D'),
  (5, 2, 'E'),
  (6, 2, 'F'),
  (7, 3, 'G'),
  (8, 2, 'H');

To extract the first two rows of each section_id sorted by the name column, you can use the following query:

SELECT
  * 
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r,
    t.*
  FROM
    xxx t) x
WHERE
  x.r <= 2;

This query uses the ROW_NUMBER() window function to assign a unique row number to each grouping, starting from 1. By partitioning the data by section_id and sorting the rows by name within each grouping, it assigns consecutive row numbers to the sorted records. Finally, the outer query filters out rows with row numbers greater than 2, effectively selecting the first two rows for each section_id.

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