|
There are several ways to do this. Here are two. The first,
which to me is the easiest to understand, and therefore to write, is:
select T.account_num
, T.payment_date
, T.payment_amt
from accounts as T
where
( select count(*)
from accounts
where account_num = T.account_num
and payment_date > T.payment_date ) < 3
Look first at the subquery. It is correlated,
which means that it is evaluated separately for each row of the
outer query. So since the outer query retrieves each row of the table,
this means the subquery will, for each row of the table,
count the number of rows, for the same account, which have a later date.
Obviously, this count will be
zero for the latest date, because there is no later payment, since it's the latest.
The count will be one for the second-latest date, because there
is only one that is later than the second-latest, and two for the third-latest date,
because there are only two that are later than the third-latest.
So these are the only rows which satisfy the WHERE clause.
The second method is somewhat similar.
select T.account_num
, T.payment_date
, T.payment_amt
from accounts as T
inner
join accounts as T2
on T2.account_num = T.account_num
and T2.payment_date > T.payment_date
group
by T.account_num
, T.payment_date
, T.payment_amt
having count(*) < 3
Each row of T is paired with all rows of T2, for the same account,
which have a later date. GROUP BY collapses the results of the join
back to the single row from T, and HAVING ensures the desired groups
(i.e. rows of T) are retained based on the count.
|
