Delete all but greatest-n-per-group in Postgres

Question

If I have two Postgres tables, say

CREATE TABLE store (
    id text,
    name text
)

CREATE TABLE product (
    store_id text,
    id text,
    created_at timestamp
)

I know I can get a table with the newest product with

SELECT product.*
FROM store JOIN (SELECT DISTINCT ON (store_id)
                 FROM product ORDER BY store_id, created_at DESC)
           AS newest_product_by_store
ON newest_product_by_store.store_id = store.id

How, then, would I do a DELETE USING that deletes every product not in newest_product_by_store?

Answer 1

The bullet proof way :

delete from product
where id not in (
    select a.id
    from (
        select p.id
        from product p
        inner join (
            select store_id, max(created_at)
            from product
            group by store_id
            ) b
            on p.store_id = b.store_id
                and p.created_at = b.created_at
        ) a
    )

---

Assuming your ID are auto-increment and you can't insert created_at back in time :

delete from product 
where id not in (
  select a.id 
  from (
    select max(id) as id
    from product 
    group by store_id
    ) a
)

Answer 2

Here is one method:

delete from product p
    using (SELECT DISTINCT ON (store_id) p.*
          FROM product p
          ORDER BY store_id, created_at DESC
         ) ps
    where p.store_id = ps.store_id and p.created_at < ps.created_at;

This would often be written without the subquery:

delete from product p
    where p.created_at < (select max(p2.created_at) from product p2 where p2.store_id = p.store_id);