14

I have a SQL statement

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

The relationship is 1-N: user may have many files.

This effectively selects the second 10-element page.

The problem is this query limits/offsets a joined table, but I want to limit/offset distinct rows from the first (users) table.

How to? I target PostgreSQL and HSQLDB

Queequeg
  • 2,824
  • 8
  • 39
  • 66
  • I think you are asking for the second page of users (page size 10) who own files of type JPG. Test this on a data set where one user owns 10 jpg files. I think you will get one unique user, 10 rows. – chrisinmtown Mar 28 '18 at 13:49

2 Answers2

23

You need to limit the select on the outer table first and then join the dependent table to the result.

select * from (select * from users where f.mime_type = 'jpg' limit 10 offset 10)  as u
left join files f
   on u.id = f.user_id
jonathancardoso
  • 11,737
  • 7
  • 53
  • 72
Udo Klein
  • 6,784
  • 1
  • 36
  • 61
  • can you `order by` in the sub-select and add `where` clause on the `files` table? – Queequeg Apr 09 '13 at 09:07
  • Yes, but ordering by the sub-select has different semantics depending on how you do it (inside or outside of the sub-select). But you can do even both together. – Udo Klein Apr 09 '13 at 10:21
  • I think this will select only the users with files with f.mime_type = 'jpg' for the tenth first users. It means that you will not have 10 results, but N results with N < or equals to 10, but taken between ten users (according to the offset). – lpratlong Jan 25 '18 at 15:33
  • -1. Baffled that @Queequeg accepted this answer bocs it discards the JPG criteria. If you limit the user count in an inner query to 10 the outer could yield zero users who own JPG files. – chrisinmtown Mar 28 '18 at 13:51
-1

You can also use GROUP_CONCAT() and GROUP BY to paginate and reduce the number of rows returned.

select u.id, u.name, GROUP_CONCAT(f.id) as file_ids
from users u left join files f
on u.id = f.user_id
where f.mime_type = 'jpg'
group by u.id
order by u.join_date desc
limit 10 offset 10

To combine multiple columns use this

select u.id, u.name, GROUP_CONCAT(f.id, '|', f.name) as file_ids
from users u left join files f
on u.id = f.user_id
where f.mime_type = 'jpg'
group by u.id
order by u.join_date desc
limit 10 offset 10

Also have a look at this and this

Sagar Mc
  • 19
  • 5
  • The link you gave is for MySQL, are you sure that the same mentioned in the question – Dordi Dec 20 '22 at 13:25
  • yes, it is MYSQL Link, and the question targets PostgreSQL, but I am suggesting that it could be done in the above manner and similar to group_concat() in PostgreSQL is string_agg(). I was using MYSQL and came across this problem and solved it like the above.. – Sagar Mc Jan 12 '23 at 10:23