LIMITE groupée dans PostgreSQL: affichez les N premières lignes pour chaque groupe?

Je dois prendre les N premières lignes pour chaque groupe, classées par colonne personnalisée.

Vu le tableau suivant:

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) 

J’ai besoin des 2 premières lignes (classées par nom ) pour chaque identificateur de section , soit un résultat similaire à:

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

J’utilise PostgreSQL 8.3.5.

Nouvelle 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 xr < = 2; 

Depuis v9.3 vous pouvez faire une jointure latérale

 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; 

Cela pourrait être plus rapide mais, bien sûr, vous devriez tester les performances spécifiquement sur vos données et votre cas d’utilisation.

Voici une autre solution (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 ;