Skip to content
Advertisement

Postgres, how to limit number of rows returned from joined tables

I have the following query that return the data I want, however for the joined tables, I want to limit the number of rows returned and preferrably be able to specify for each joined table. I tried using limit with the select itself, but doesn’t seem to be supported.

Is this possible? I am using Postgres 11.

UPDATED here is my new query. I simplified it, but the issue is it’s no longer returning correct data. For the field_4 field, it’s returing rows/data that isn’t associated with the record. Do I have something wrong?

Advertisement

Answer

One approach is to turn each left join to a lateral join; you can then set the limit within each subquery:

Note that you need an order by to go along with limit in order to get stable results – you can replace the question marks in the query with the revelant columns or set of columns.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement