Q
Problem solve Get help with specific problems with your technologies, process and projects.

Latest three payments for each account in SQL

I have a table with account_num, payment_date and payment_amt. I only want to display the three latest payments for each account. How do I do this in SQL?

I have a table with account_num, payment_date and payment_amt. I don't know how to get the payment details which...

I only want to display three latest payments for each account. I hope that you could help me. Thank you.

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.

This was last published in April 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close