Finding the Last Transaction for a Customer

Published: January 31, 2007

Hey, Howard:

I've been studying your book, SQL at Work. I was hoping you would answer a question for me. We have a historical transaction file sorted on a descending date field. I'd like to only select the latest record for a specific ID with the most current date. I don't know the value of this date and I don't want it to return any other records. Is this possible? Thanks!

--Mary

This kind of query can be done using a subquery or a join operation. Let's create a sample table, load it with data, and try a few queries that demonstrate this technique. The following statement will create a transaction file that contains debit and credit transactions, customer identification number, and a quantity.

CREATE TABLE SQLBOOK.TRANSTEST
	(TRANSID INTEGER NOT NULL 
PRIMARY KEY 
GENERATED ALWAYS 
AS IDENTITY,
	 TRANSDATE DATE NOT NULL,
	 CUSTOMERID INTEGER NOT NULL,
	 TRANSTYPE CHAR(1) NOT NULL,
	 QUANTITY DECIMAL(10,2) NOT NULL);

Now execute the following statements to add data to the test table:

INSERT INTO SQLBOOK.TRANSTEST 
	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
	VALUES ('01/01/1980',1,'C',100.33);
INSERT INTO SQLBOOK.TRANSTEST 
	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
	VALUES ('02/02/1982',1,'D',10.21);
INSERT INTO SQLBOOK.TRANSTEST 
	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
	VALUES ('03/03/1983',1,'C',1000.54);
INSERT INTO SQLBOOK.TRANSTEST 
	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
	VALUES ('04/04/1984',1,'D',830.74);
INSERT INTO SQLBOOK.TRANSTEST 
	(TRANSDATE,CUSTOMERID,TRANSTYPE,QUANTITY)
	VALUES ('05/05/1985',1,'C',52.12);

Ok, now that we have data, let's think about a query strategy. In order to answer the query, you need two pieces of information, the customer number that you want to find the last transaction for and the date of the last transaction. To get the date of the last transaction for a customer, you can execute the following query:

SELECT MAX(TRANSDATE) 
FROM SQLBOOK.TRANSTEST
WHERE CUSTOMERID = 1;

Now, this query returns a single row with the date of the last transaction, to find that transaction record, we can join the result of this query to a query against the TRANSTEST file to find the rest of the field values in the target record. One syntax for accomplishing this is the following query:

SELECT * 
	FROM SQLBOOK.TRANSTEST AS A 
		INNER JOIN
			(SELECT MAX(TRANSDATE) AS MAXDATE 
				FROM SQLBOOK.TRANSTEST 
				WHERE CUSTOMERID = 1) AS B
		ON (MAXDATE = TRANSDATE)
	WHERE CUSTOMERID = 1 ;

Note that we open the TRANSTEST table and join it to the results of our max date query. This type of query can also be accomplished using a subquery. Here is an example:

SELECT * 
	FROM SQLBOOK.TRANSTEST AS A
	WHERE CUSTOMERID = 1 
	AND TRANSDATE = 
		(SELECT MAX(TRANSDATE) 
		FROM SQLBOOK.TRANSTEST AS B
		WHERE B.CUSTOMERID = A.CUSTOMERID);

Note that this is equivalent to what is being performed in the first example, in fact the iSeries will recognize that the subquery is actually a join operation and choose to implement the query as a join. Hope this helps!

--Howard

Howard Arner is vice president of Client Server Development. He spends his days writing software that talks to the System i5, iSeries, and AS/400 databases from Microsoft's .NET protocols. His book, SQL at Work is available at the IT Jungle online bookstore.

RELATED STORY

Retrieve a Representative of a Group

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot