How to get the most recent document per product?

Question

I have the following simplified tables:

Product: [uniqueId|name]
ProductAttachment: [FK productId|FK attachmentId]
Attachment: [uniqueId|createdOn|name]

The product and attachment tables are in an n:m relationship with productAttachment being the intermediate table. createdOn isn't guaranteed to be unique for each attachment.

I am trying to get the most recent attachment for each product. I can join the tables but I don't know how to get the full attachment row for each product without getting multiple rows per product. This is part of a much larger query so ideally I'd like to do this without having to change the rest of the query. If i could get the attachmentId from the subquery I could use it in a separate join.

I've looked at some similar questions such as:

SQL select only rows with max value on a column

and

Finding the most recent timestamp per event

but as far as I understand those answers don't work for an n:m relationship. I've tried using an aggregate-subquery but I only get the attachment's creation date, if I add the attachmentId to the GROUP BY clause I will get multiple attachments per product.

SELECT p.UniqueId, mostRecentAttachment.created
  FROM ps.product AS p
       LEFT JOIN (SELECT pa.productId, MAX(a.created) AS created
                     FROM ps.productAttachment AS pa
                          INNER JOIN bs.attachment AS a ON pa.attachmentId = a.uniqueId
                    GROUP BY pa.productId) AS mostRecentAttachment ON mostRecentAttachment.productId = p.uniqueId
 WHERE p.active

I'm expecting one row per product (there are ~12000 products) and the most recent attachment if the product has one.

S-Man · Accepted Answer · 2019-07-15T10:08:10.197

demo:db<>fiddle

Using DISTINCT ON allows you to filter only the first element of an ordered group. Here the groups are the products. After joining the attachements, you can order their attachments descending by their date. So in each product group the most recent attachment becomes first. This record is taken by the DISTINCT ON clause.

SELECT DISTINCT ON (p.unique_id)
    p.*,
    a.*
FROM
    products p 
JOIN product_attachment pa ON p.unique_id = pa.product_id
JOIN attachments a ON pa.attachment_id = a.unique_id
ORDER BY p.unique_id, a.created_on DESC

How to get the most recent document per product?

1 Answers1