Purchase Price base on Latest Trans Date of each item from a same Item Purchase Table

Question

I have a query which I want to list all latest transaction date for the purchase price for each stock item.

I used aggregate MAX() to list all latest transaction for each item without including the price and it works perfect. But when I included the price, the results was having multiple dates with different prices for each item.

So I try to use sub query but it resulted in error.

Below is my query using a sample item as a test

select
  a.a7itno as "Item No", a.a7appr as "Unit Price", 
  b.maxtrdate as "Trans Date"
from m3edbprod.fcaavp a
Left Join
( select a7itno,max(a7trdt)as "maxtrdate"
from m3edbprod.fcaavp
group by a7itno) b
on a.a7itno=b.a7itno and a.a7trdt=b.maxtrdate
where a.a7itno='110ABC452'

The error appears when run:

Error: SQL0205 - Column MAXTRDATE not in table B in *N. (State:S0022, Native Code: FFFFFF33)

The Expected results should output each item with a single price and latest transdate such as tabulated below :

Item No  Unit Price Trans Date
-----------------------------
110ABC452    100.00      20210920

Note: the date is in YYYYMMDD, if I can set it to date format like 20/09/2021 or 20-09-2021 will also be good.

Will appreciate if I could get some advise from here.

Answer 1

Using MAX as a subquery to get the most recent date

SELECT  
  a.a7itno AS "Item No",
  a.a7appr AS "Unit Price",
  a.a7trdt AS "Trans Date"
FROM m3edbprod.fcaavp a
WHERE a7trdt = (SELECT
                 MAX(b.a7trdt)
                 FROM m3edbprod.fcaavp b
                 WHERE b.a7itno = a.a7itno
                )
AND a.a7itno = '110ABC452'

Answer 2

Finally, I get the desired result thanks to this query :

SELECT a.A7ITNO, a.A7APPR, a.A7TRDT
FROM m3edbprod.FCAAVP a
INNER JOIN (select A7ITNO, max(A7TRDT*100000+A7RGTM) maxdatetime FROM m3edbprod.FCAAVP group by A7ITNO) b
 ON a.A7ITNO = b.A7ITNO and a.A7TRDT*100000+a.A7RGTM = b.maxdatetime
AND a.a7itno='110ABC452'