Last transaction date & POCost

[Guest]

I need to get the last POCost from the transaction log from 2 tables:
TrxnHeader and TxnDetail.
Fields from TxnHeader: TxnType, TxnDate, TxnGroup
Fields from TxnDetail: PartNumber, POCost, TxnGroup

I have created a single tier query as follows, but wanted to make sure that
it is a valid query. I'm having doubts after reading some of the posts that
have similar requirements, but took a two-tiered query approach.

Here's my one tier query. Is this valid / correct?

SELECT TxnDetail.PartNumber, Last(TxnHeader.TxnDate) AS LastOfTxnDate,
Last(TxnDetail.POCost) AS LastOfPOCost, TxnHeader.TxnType
FROM TxnDetail INNER JOIN TxnHeader ON TxnDetail.TxnGroup = Txn.TxnGroup
GROUP BY TxnDetail.PartNumber, TxnHeader.TxnType
HAVING (((TxnHeader.TxnType)="POR"))
ORDER BY Last(TxnHeader.TxnDate);

Any constructive advice is very much appreciated.

 

[Guest]

In a single query you should use a subquery to identify the latest date per
TxnType, using aliases to identify the two instances of the TxnHeader table:

SELECT TxnDetail.PartNumber, TH1.TxnDate
TxnDetail.POCost, TH1.TxnType
FROM TxnDetail INNER JOIN TxnHeader AS TH1
ON TxnDetail.TxnGroup = TH1.TxnGroup
WHERE TxnHeader.TxnType ="POR"
AND TH1.TxnDate =
(SELECT MAX(TxnDate)
FROM TxnHeader AS TH2
WHERE TH2.TxnType = TH1.TxnType);

In effect this does the same as joining two queries.

Concepts like 'first' and 'last' are pretty meaningless in a relational
database which is set oriented; and sets by definition have no intrinsic
order. You are really talking about the 'latest' date here, and this is
obtained from the values of the dat by means of the MAX function.

Ken Sheridan
Stafford, England