Postgresql limit by N groups

Question

create table t (a integer, b text);
insert into t values (1, 'a'), (2, 'a'), (3, 'a'), (10, 'b'), (11, 'b'), (0, 'c');

I need to limit select by 2 first groups of column b (1 - a, 2 - b);

select * from t order by b;
 a  | b 
----+---
  1 | a
  2 | a
  3 | a
 10 | b
 11 | b
 -- limit here
  0 | c
(6 rows)

Something like where b in (select distinct(b) from t order by b limit 2) whithout subquery.

Real case - b is not a table. It's a complex query with joins and filters. With subquery all filters applied to resultset twice. Like select top two auhtors and them books where book name like '%winter%'. — Ziav
– Ziav, Commented Mar 21, 2019 at 10:56
So, the column a- is it always incremental? How exactly do you know at what point to start counting for b? — Kaushik Nayak
– Kaushik Nayak, Commented Mar 21, 2019 at 11:51

Rémy Baron · Accepted Answer · 2019-03-21 12:43:36Z

3

you can use windows functions like dense_rank :

select *,dense_rank()  over (order by  b) group_number from t order by b

so you can do :

select * from (
    select *,dense_rank() over (order by  b) group_number  from t order by b
) a where group_number<=2

answered Mar 21, 2019 at 12:43

Rémy Baron

1,4099 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Exactly! Thank you.