How to query for MAX value, by CA_ID, while pulling field from a third table

[b***@omegasoftwareinc.com]

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 each CA_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?

[Gert-Jan Strik]

Post by b***@omegasoftwareinc.com
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 each CA_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?

A more a less direct translation is this (see below). Please check if
the combination of CA_ID - TRX_DATE is unique, otherwise you might get
unexpected results.

SELECT T1.CA_ID, T1.max_date AS TRX_DATE, TRX_DESC
FROM (
SELECT CA_ID, MAX(TRX_DATE) AS max_date
FROM Table1
GROUP BY CA_ID
) AS T1
JOIN Table1 AS T2
ON T2.CA_ID = T1.CA_ID
AND T2.TRX_DATE = T1.max_date
JOIN Table2
ON PK_TABLE2_ID = T2.FK_TABLE2_ID

[b***@omegasoftwareinc.com]

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

[rpresser]

Post by b***@omegasoftwareinc.com
Actually, there could very well be multiple records, for the same CA_ID, with the same TRX_DATE. Any ideas in that case?

You would need to decide which TRX_DESC you want returned if there is a match.