SQL. Join with filter and offset

I need to create a SQL query that will return images with tags. The result should be ordered by some column and filtered by UserID and tags. The result should be paginated.

There is a SQL statement

SELECT img.*, t.Name
FROM Image img
INNER JOIN ImageTag it on it.ImageID = img.ImageID
INNER JOIN Tag t on t.TagID = it.TagID
WHERE img.UserID = 1 and t.Name in ('first','second')
ORDER BY img.CreatedAt OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

This request is not working as expected. I expect to get 50 images, but they will be combined with tags and the result will be something like …

| Image.ImageID | Tag.Name | ... |
| 1             | First    | ... |
| 1             | Second   | ... |
| 2             | First    | ... |
| 3             | First    | ... |
....

How do I get 50 images instead of 50 rows?

Answer

You can change the order by to interleave the images:

ORDER BY ROW_NUMBER() OVER (PARTITION BY img.ImageID ORDER BY NEWID()),
         img.CreatedAt
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Basically, this enumerates the rows for each image, returning the “first” row. Note: If you don’t have 50 images, then there will be duplicates as the code moves to the second tag.

If you want only one row per image with all tags, you can use APPLY:

SELECT img.*, t.tags
FROM Image img CROSS APPLY
     (SELECT STRING_AGG(t.Name, ',') as tags
      FROM ImageTag it JOIN
           Tag t 
           ON t.TagID = it.TagID
      WHERE it.ImageID = img.ImageID AND
            t.Name IN ('first', 'second')
     ) t
WHERE img.UserID = 1 
ORDER BY img.CreatedAt OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

This aggregates the tags for each image in the subquery, so only one row per image is returned. This concatenates the tags together. Of course, you could use other aggregation functions, such as MIN() or MAX().

You can also use:

MIN(t.Name) + (CASE WHEN MIN(t.Name) <> MAX(t.Name) THEN ',' + MAX(t.Name) ELSE '' END)