CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
user779159
user779159

Reputation: 9602

Aggregate with a limit in postgres

In this query, each row of table a could have hundreds of rows of table b associated with it. So the array_agg contains all of those values. I'd like to be able to set a limit for it, but instide array_agg I can do order by but there's no way to set a limit.

select a.column1, array_agg(b.column2)
from a left join b using (id)
where a.column3 = 'value'
group by a.column1

I could use the "slice" syntax on the array but that's quite expensive since it first has to retrieve all the rows then discard the other ones. What's the proper efficient way to do this?

Upvotes: 3

Views: 3711

Answers (3)

Per Johansson
Per Johansson

Reputation: 6877

In many cases you can create an array from a subselect using the ARRAY keyword (near the bottom of the linked heading).

select a.column1, ARRAY(select column2 from b where b.id = a.id limit 10)
from a
where a.column3 = 'value'
group by a.column1

Upvotes: 0

jjanes
jjanes

Reputation: 44177

I would use a lateral join.

select a.column1, array_agg(b.column2)
from a left join lateral 
    (select id, column2 from b where b.id=a.id order by something limit 10) b using (id)
where a.column3 = 'value'
group by a.column1

Since the "id" restriction is already inside the lateral query, you could make the join condition on true rather than using (id). I don't know which is less confusing.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269783

I think you need to count first and then aggregate:

select a.column1, array_agg(b.column2)
from (select a.column1, b.column2,
             row_number() over (partition by a.column1 order by a.column1) as seqnum
      from a left join
           b 
           using (id)
      where a.column3 = 'value'
     ) a
where seqnum <= 10
group by a.column1

Upvotes: 2

Related Questions