Select the Most Recent N Transactions for Each Account

Problem

There is a customer purchase table that contains many transactions as show below. We want to only select the most recent 10 transactions for each customer. How do we do it?

SQL> select * from V_ALL_TXNS where rownum <20;

CUSTOMER_ID          TXN_DT       TXN_AMT
-------------------- --------- ----------
11738368607          02-DEC-10       4.59
11738368607          06-DEC-10        115
11738368607          06-DEC-10      12.04
11738368607          06-DEC-10        8.7
11738368607          06-DEC-10      15.96
11738368607          07-DEC-10          7
11738368607          07-DEC-10          8
11738368607          07-DEC-10         50
11738368607          13-DEC-10       2.12
11738368607          23-DEC-10      14.95
11738368607          28-DEC-10      209.8
11738368607          28-DEC-10      18.41
11738368607          30-DEC-10      43.18
11738368607          29-DEC-10      49.98
11738368607          29-DEC-10       9.83
11738368607          28-DEC-10      11.65
11738368607          30-DEC-10         20
11738368607          06-DEC-10      30.79
11738368607          01-DEC-10      19.23

Solution

We use Oracle analytic function row_number() to generate rank for each customer_id based on the transaction time. The query is shown below.

SQL> with
    tbl_temp as
    (select a.*,
    row_number() over 
    (partition by customer_id order by txn_dt desc) as rnk
    from V_ALL_TXNS a)
    select customer_id, txn_dt, txn_amt from
    tbl_temp
    where rnk <=10
    order by customer_id, txn_dt desc;

CUSTOMER_ID          TXN_DT       TXN_AMT
-------------------- --------- ----------
11685988069          30-DEC-10      269.5
11685988069          30-DEC-10     150.56
11685988069          30-DEC-10      46.66
11685988069          29-DEC-10      12.94
11685988069          29-DEC-10      16.04
11685988069          28-DEC-10      18.33
11685988069          28-DEC-10      23.33
11685988069          23-DEC-10     174.71
11685988069          22-DEC-10      23.76
11685988069          20-DEC-10     194.79
11738368607          30-DEC-10      43.18
11738368607          30-DEC-10       15.2
11738368607          30-DEC-10         20
11738368607          29-DEC-10      49.98
11738368607          29-DEC-10       9.83
11738368607          28-DEC-10      209.8
11738368607          28-DEC-10      11.65
11738368607          28-DEC-10      18.41
11738368607          23-DEC-10      14.95
11738368607          20-DEC-10      36.99
11768488237          31-DEC-10       6.69
11768488237          31-DEC-10       4.62
11768488237          31-DEC-10        1.4
11768488237          30-DEC-10       6.69
11768488237          30-DEC-10        1.4
11768488237          29-DEC-10       2.92
11768488237          29-DEC-10        1.7
11768488237          29-DEC-10        2.8
11768488237          29-DEC-10       3.55
11768488237          28-DEC-10        1.7