Search code examples
sqlpostgresqlsubquerygreatest-n-per-groupcorrelated-subquery

How to query most recent five transactions per item in sql

There is a transaction table and in there there are columns such as transaction id, name, and createdat. There are multiple transactions for each name whose createdat data will be all different.

I want to query for five most recent transactions per name. Not get five most recent transactions in the whole table. In the case that it has less than five transactions then I want it to just show whatever is there.

Trying to muster all my sql knowledge but i'm having difficulty doing it. Any input will be highly appreciated.

Solution

  • try this query,

    select * 
from ( 
   select *, row_number() OVER (PARTITION BY name ORDER BY createdat DESC) as rnk 
   FROM yourtable
) t 
where rnk <=5