Joe Celko's SQL for Smarties, 3rd Edition

33.15. Temporary Tables Are Sometimes Handy

Another trick is to use temporary tables to hold intermediate results to avoid CROSS JOINs and excessive recalculations. A materialized VIEW is also a form of temporary table, but you cannot index it. In this problem, we want to find the total amount of the latest balances in all our accounts.

Assume that the Payments table holds the details of each payment and that the payment numbers are increasing over time. The Accounts table shows the account identification number and the balance after each payment is made. The query might be done like this:

SELECT SUM(A1.balance) FROM Accounts AS A1, Payments AS P1 WHERE P1.acct_nbr = A1.acct_nbr AND P1.payment_nbr = (SELECT MAX(payment_nbr) FROM Payments AS ...