tablas seleccionar relacionar registros registro primer obtener grupo desde contar consultas como comandos cero cada aprender agrupados sql postgresql

seleccionar - LÍMITE agrupado en PostgreSQL: ¿muestra las primeras N filas para cada grupo?



select en postgresql (5)

Necesito tomar las primeras N filas para cada grupo, ordenadas por columna personalizada.

Dada la siguiente tabla:

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)

Necesito las primeras 2 filas (ordenadas por nombre ) para cada section_id , es decir, un resultado similar a:

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

Estoy usando PostgreSQL 8.3.5.


Aquí hay otra solución (PostgreSQL <= 8.3).

SELECT * FROM xxx a WHERE ( SELECT COUNT(*) FROM xxx WHERE section_id = a.section_id AND name <= a.name ) <= 2


Desde v9.3 puedes hacer una unión lateral

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;

Puede ser más rápido , pero, por supuesto, debe probar el rendimiento específicamente en sus datos y caso de uso.


Nueva solución (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;


-- 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 ;


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)