I came across a situation where I need to run an Oracle query that would only show the first n number of records within a group. Let me give you an example. Consider a table that stores a list of transactions. A customer can have any number of transactions attached to their customer profile, but we only want to get the most current 2 transactions per customer. The query I used to get my results was as follows (reports the customer name and the amount of their last 2 transactions):
select * from (select c.name, t.tranamt, row_number() over (partition by c.id order by t.id, t.txndate desc) rn from transaction t, customer c where c.ID = t.CUSTOMER_ID) where rn <= 2
It took some time, but certainly a very handy query to keep in the archives.
For what it’s worth,