sql - Proper pagination in a JOIN select -

i have sql statement

select *  users u left join files f on u.id = f.user_id f.mime_type = 'jpg' order u.join_date desc limit 10 offset 10

the relationship 1-n: user may have many files.

this selects second 10-element page.

the problem query limits/offsets joined table, want limit/offset distinct rows first (users) table.

how to? target postgresql , hsqldb

you need limit select on outer table first , join dependent table result.

select * (select * users limit 10 offset 10)  u left join files f    on u.id = f.user_id

Kiastu