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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.