Grouped LIMIT in PostgreSQL: show the first N rows for each group?

postgresql, sql

Solution

New solution (PostgreSQL 8.4)

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

Problem

I need to take the first N rows for each group, ordered by custom column. Given the following table: ``` db=# SELECT * FROM xxx; 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 (8 rows) ``` I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to: ``` id | section_id | name ----+------------+------ 1 | 1 | A 2 | 1 | B 5 | 2 | E 6 | 2 | F 7 | 3 | G (5 rows) ``` I am using PostgreSQL 8.3.5.

Original source

Related problems