## Saturday, November 23, 2013

### 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
```