Select rows based on latest date

Question

This are the tables:

• Material (Code, ID)
• Itemtrans (IteID, FtrID, Tradecode)
• Fintrade (Ftrdate, ID)

---

• Material.ID=Itemtrans.IteID
• Itemtrans.FtrID=Fintrade.ID

I want to list the last record on all the codes (Material.Code), tradecodes (Itemtrans.Tradecode) and Ftrdate (Fintrade.Ftrdate) based on Fintrade.Ftrdate (the date when registered).

I made this, but i think i am losing something :(

SELECT
         MAT.CODE COD_ARTICOL,
         FTR.FTRDATE DATA_DOCUMENT,
         FTR.TRADECODE DOCUMENT
FROM ITEMTRANS ITM
LEFT JOIN
  ( SELECT 
        ID, TRADECODE,
        MAX(FTRDATE) AS maxFTRdate
    FROM FINTRADE
    GROUP BY ID
  ) AS x ON x.ID = ITM.ITEid
LEFT JOIN FINTRADE FTR ON  FTR.id = x.id 
                   AND FTR.FTRDATE = x.maxFTRdate 

LEFT JOIN MATERIAL MAT ON MAT.id = ITM.ITEID
LEFT JOIN FINTRADE FTR ON FTR.ID = ITM.FTRID

I need some help :(

Answer 1

SELECT m.code,
       i.TradeCode,
       f.Ftrdate 
       FROM ( SELECT MAX( Fintrade.Ftrdate ) as Ftrdate,
                     Fintrade.ID as ID
                     FROM Fintrade
            GROUP BY Fintrade.Ftrdate )  as f
INNER JOIN (SELECT itemtrans.iteID as iteID,
                   itemtrans.FtrID as FtrID,
                   MAX( itemtrans.TradeCode ) as TradeCode
            FROM itemtrans 
            GROUP BY itemtrans.FtrID ) as i on f.ID = i.FtrID
INNER JOIN ( SELECT MAX( material.code ) as code,
                    material.id as id
            FROM material
            GROUP BY material.id ) as m
                    on i.iteID = m.id