1

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 > 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 ?

  • 1
    What does TB_ stand for? – Philᵀᴹ Mar 1 '16 at 22:12
  • I've got money on "Table" – paulbarbin Mar 1 '16 at 22:19
  • @Phil TB_ is the table name prefix – mauriciosouza Mar 1 '16 at 22:32
  • I corrected a portion of the join what was written wrong – mauriciosouza Mar 1 '16 at 22:36
  • could you please add the expected output in the question. – JSapkota Mar 2 '16 at 1:31
1

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 > 0
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.