How to mount a query to join by searching by last date
One way to do it is to use CTE (but I haven't tested the syntax myself). For example:


WITH ANSWER AS
(SELECT TB_COSTS.COD_PROD
    , TB_COSTS.DATE_ATT
    , TB_COSTS.COST
    , ROW_NUMBER() OVER (PARTITION BY TB_COSTS.COD_PROD ORDER BY DATE_ATT DESC) AS ORDER_VALUE
FROM TB_COSTS)

SELECT TB_PRODUCTS.ID
, TB_PRODUCTS.COD_PROD
, TB_PRODUCTS.Name
, ANSWER.DATE_ATT
, ANSWER.COSTS
FROM TB_PRODUCTS
INNER JOIN ANSWER on TB_PRODUCTS.COD_PROD = ANSWER.COD_PROD
AND ANSWER.ORDER_VALUE = 1
WHERE TB_COSTS.COST &gt; 0