How to mount a query to join by searching by last date I have the following tables:
TB_PRODUCTS

ID     COD_PROD     Name  
===============================================
1       100    Video monitor
2       101    Keyboard
3       102    Mouse


TB_COSTS

COD_PROD    DATE_ATT    COST
===============================================
100        2016-03-01    1.00
100        2016-02-29    2.00
101        2016-03-01    1.00
101        2016-02-29    2.00
102        2016-03-01    1.00
102        2016-02-29    2.00


This is the query that I have:

SELECT
TB_PRODUCTS.ID,
TB_PRODUCTS.COD_PROD,
TB_PRODUCTS.Name,
TB_COSTS.DATE_ATT,
TB_COSTS.COST
FROM
TB_PRODUCTS
INNER JOIN TB_COSTS ON TB_COSTS.COD_PROD = TB_PRODUCTS.COD_PROD
WHERE
TB_COSTS.COST &gt; 0


But the query does not return me the cost of the last date, and what I need is to do a join with the cost of the last date.
How could I filter the Join only taking the cost of the most recent 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