CODEWITHSUNDEEP
sqlpostgresqlinner-joingreatest-n-per-group
ProGM
ProGM

Reputation: 7108

Inner join a table using limit

I have a 1-n relation between collections and images tables.

My goal is to select all collections given a condition together with the top 3 images of any collection found.

How I do it now:

Query on collections:

SELECT * FROM "collections" WHERE SOME_CONDITION

After selecting all collections, I run a query like this for each collection:

SELECT * FROM "images" WHERE "images"."collection_id" = MY_CURRENT_COLLECTION_ID LIMIT 3

My goal is to do it in one query:

SELECT "images".*
    ,"collections".*
FROM "collections"
INNER JOIN (SOME_MAGIC_TO_GET_THE_TOP_THREE_IMAGES_FOR_EACH_COLLECTION) AS "images" ON "images"."collection_id" = "collections"."id"
WHERE SOME_CONDITION

Any idea?

P.S. I'm using postgresql.

Upvotes: 1

Views: 138

Answers (1)

user330315
user330315

Reputation:

This can be done using a window function:

SELECT img.*, 
       collections.*
FROM collections 
  JOIN (
    select *, 
           row_number() over (partition by collection_id order by something) as rn
    from images
  ) img ON img.collection_id = collections.id AND img.rn <= 3
WHERE ....;

Note that assigning a row_number() only works in a stable way if you can order the rows by something. The something in the statement above is any column in your images table that will enable you to properly sort those rows, e.g. the last modification date, or the creation date or something similar. You apparently need to replace something with an existing column.

Upvotes: 3

Related Questions