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

Question

I need to take the first N rows for each group, ordered by a 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.

Answer 1

For PostgreSQL 8.4, You can use the below code:

SELECT * 

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS r,t.* FROM xxx t) x

WHERE x.r <= 2;