I have a Transactions table that stores card transaction data. It has the following columns: TransactionID, TransDatetime,...
CardNumber, Balance. TransactionID is a primary key that gets incremented with every transaction. Transactions don't occur every day. The Balance is adjusted with every transaction and reflected in the corresponding column. I need to find the latest transaction for the card before a specified date. How do I do that using a SQL statement?
The method which is easiest to understand (for me, anyway) is the correlated subquery.
select TransactionID , TransDatetime , CardNumber , Balance from Transactions as T where TransDatetime = ( select max(TransDatetime) from Transactions where CardNumber = T.CardNumber and TransDatetime < '2006-10-01 09:37:00' )
Each row in the outer query is evaluated based on the WHERE clause. If the WHERE clause evaluates TRUE, then that row is placed into the query result set. The subquery is correlated because in order to evaluate it, it needs to use the value of T.CardNumber, which is the value from the row in the outer query. Read the WHERE clause like a normal English sentence a few times, and it will soon make sense. In fact, it comes very close to matching the original requirement ("the latest transaction for the card before a specified date").
Another way to do it is with a join.
select T.TransactionID , T.TransDatetime , T.CardNumber , T.Balance from Transactions as T inner join ( select CardNumber , max(TransDatetime) as maxDateTime from Transactions where TransDatetime < '2006-10-01 09:37:00' group by CardNumber ) as M on M.CardNumber = T.CardNumber and M.maxDateTime = T.TransDatetime
In this query, the maximum datetimes are in a separate table, a so-called derived table (because it is derived as the result of a query). The derived table contains the maximum TransDatetime for each CardNumber. Note that the join is on two columns.
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.