LIMITE raggruppato in PostgreSQL: mostra le prime N righe per ogni gruppo?

Ho bisogno di prendere le prime N righe per ogni gruppo, ordinate per colonna personalizzata.

Data la seguente tabella:

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) 

Ho bisogno delle prime 2 righe (ordinate per nome ) per ogni section_id , cioè un risultato simile a:

  id | section_id | name ----+------------+------ 1 | 1 | A 2 | 1 | B 5 | 2 | E 6 | 2 | F 7 | 3 | G (5 rows) 

Sto usando PostgreSQL 8.3.5.

Nuova soluzione (PostgreSQL 8.4)

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

Dalla v9.3 puoi fare un join laterale

 select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer join lateral ( select * from t t_inner where t_inner.section_id = t_outer.section_id order by t_inner.name limit 2 ) t_top on true order by t_outer.section_id; 

Potrebbe essere più veloce , ma, naturalmente, dovresti testare le prestazioni in modo specifico sui tuoi dati e utilizzare case.

Ecco un’altra soluzione (PostgreSQL <= 8.3).

 SELECT * FROM xxx a WHERE ( SELECT COUNT(*) FROM xxx WHERE section_id = a.section_id AND name <= a.name ) <= 2 
 SELECT x.* FROM ( SELECT section_id, COALESCE ( ( SELECT xi FROM xxx xi WHERE xi.section_id = xo.section_id ORDER BY name, id OFFSET 1 LIMIT 1 ), ( SELECT xi FROM xxx xi WHERE xi.section_id = xo.section_id ORDER BY name DESC, id DESC LIMIT 1 ) ) AS mlast FROM ( SELECT DISTINCT section_id FROM xxx ) xo ) xoo JOIN xxx x ON x.section_id = xoo.section_id AND (x.name, x.id) <= ((mlast).name, (mlast).id) 
  -- ranking without WINDOW functions -- EXPLAIN ANALYZE WITH rnk AS ( SELECT x1.id , COUNT(x2.id) AS rnk FROM xxx x1 LEFT JOIN xxx x2 ON x1.section_id = x2.section_id AND x2.name <= x1.name GROUP BY x1.id ) SELECT this.* FROM xxx this JOIN rnk ON rnk.id = this.id WHERE rnk.rnk <=2 ORDER BY this.section_id, rnk.rnk ; -- The same without using a CTE -- EXPLAIN ANALYZE SELECT this.* FROM xxx this JOIN ( SELECT x1.id , COUNT(x2.id) AS rnk FROM xxx x1 LEFT JOIN xxx x2 ON x1.section_id = x2.section_id AND x2.name <= x1.name GROUP BY x1.id ) rnk ON rnk.id = this.id WHERE rnk.rnk <=2 ORDER BY this.section_id, rnk.rnk ;