How to display only the last date from those available in a column, provided that they are not all related to 1 value?

Question

SQL

Actually, the task.
And what I have already done.
sqlfiddle.com/#!6/a672a/152
The essence of the problem is that I don’t understand how exactly to combine the value in Rates.currency with Rates.date, because if it’s trite to do:

WHERE I.item_id = 'number'
AND R.date = (SELECT MAX(date) 
                       FROM Rates
                       )

That comes out nonsense.
Or maybe I went the wrong way?

Answer 1

You can try to specify a condition on the currency/maximum date in the join, like this:

SELECT
  item_id,
  I.price * COALESCE(R.rate, 1) price_in_byr 
FROM Items AS I
LEFT JOIN Rates AS R ON
  I.currency = R.currency AND
  R.date = (SELECT MAX(date) FROM Rates WHERE Rates.currency = I.currency)
WHERE
  item_id = 5;

Answer 2

Fixed:

SELECT TOP 1 item_id, date,
 CASE I.currency
        WHEN 'BYR'
        THEN I.price
        ELSE I.price * R.rate
 END price_in_byr 
FROM Items AS I FULL JOIN Rates AS R ON I.currency = R.currency
WHERE item_id = 5 AND R.date <= (SELECT MAX(date) FROM Rates) 
ORDER BY date DESC