Let's say that I have the following two tables:

Table1
-------
TABLE1_ID
CA_ID
FK_TABLE2_ID
TRX_DATE

Table2
-------
PK_TABLE2_ID
TRX_DESC

Table1 can have multiple records with the same CA_ID. In addition, for eachCA_ID, there could be multiple records, some with different values of FK_TABLE2_ID.

What I want is a query that pulls back CA_ID, TRX_DESC, & TRX_DATE. For each CA_ID in Table 1, I want to find the record that has the MAX value for TRX_DATE. The returned TRX_DATE will be that MAX value, for each CA_ID, and the TRX_DESC will be the TRX_DESC that corresponds to that record with the MAX value for TRX_DATE (i.e. foreign key ==> Table1.FK_TABLE2_ID = Table2.PK_TABLE2_ID).

How could I construct such a query?

Actually, there could very well be multiple records, for the same CA_ID, with the same TRX_DATE. Any ideas in that case?

On Wednesday, August 22, 2012 6:26:34 PM UTC-4, (unknown) wrote:
You would need to decide which TRX_DESC you want returned if there is a match.