Limit response for each sku

March 11, 2022

I have a table called purchases and want to get purchases for each sku but only last 2 purchases for each sku.
How can I do that?

dbfiddle

my purchase table sql:

CREATE TABLE purchase
(
id SERIAL PRIMARY KEY,
sku TEXT NOT NULL,
date DATE NOT NULL
)

my desired response:

>Solution :

You can utilise row_number(). From your limited sample data it looks like you want the lowest-Id values per SKU, amend the order-by criteria if that’s not correct:

SELECT id, sku, date 
FROM (
  select *, row_number() over(partition by sku order by date desc) rn
  from purchase
)p
where rn <= 2
order by sku, date;

DB<>Fiddle