Limit response for each sku-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Like this

with p as (
select p.*, row_number() over (partition by sku order by date desc) as rn 
FROM purchase p
)
select * from p where rn <= 2 order by sku,rn

Result here