select from table1 depend on newest date in table 2

Question

I have 2 tables like above
table1 = items => id, name
table 2 = item_price => item_id, date,price
I need to

select items.name from items 
INNER JOIN item_price ON items.id = item_price2.item_id
where item_price.date is last date

items
id      name
1       item name 1
2       item name 2
3       item name 3

    item_price
    item_id  date            price
    1        2022-01-13      5
    2        2022-01-10      7
    1        2022-01-10      4
    1        2022-01-09      9

I need build query to get this result

 item name             price
 item name 1            5
 item name 2            7

thanks a lot

Registered User · Answer 1 · 2022-01-12T23:21:30.703

This can be solved by selecting the last row using the ROW_NUMBER windowed partition function.

WITH last_item_price (item_id, price, reverse_order)
AS
  (SELECT item_id
   , price
   , ROW_NUMBER() OVER 
     (PARTITION BY item_id 
     ORDER BY date DESC) AS reverse_order
  FROM item_price)
SELECT i.name
  , p.price
FROM items i
JOIN last_item_price p
  ON i.item_id = p.item_id
  AND p.reverse_order = 1;

Unless the primary key of item_price is defined as the composite key for item_id and date, there is a risk you will have more than one possible price. For instance, if the price changes three times on a single day, then how do you know which is the correct value? I would recommend using a DATETIME or DATETIME2 field to help pick the correct price in that scenario. Alternatively, you can define the primary key as the composite of the two fields and only allow a single price each day.

Another way to avoid the duplication issue is to add an auto-incrementing identity column. This will not be useful for joins and I still strongly recommend using a composite key for the item_id and date fields, but it is a valid alternative. In that case, you can modify the partition function's ORDER BY clause to:

ORDER BY date DESC, item_price_id DESC

In general, I would not recommend naming a field date. Since SQL Server has a data type called DATE, this can cause issues with linters. In some cases, you may be required to use brackets so the field is [date] to disambiguate your intent. A better name is probably price_date or price_change_date.

select from table1 depend on newest date in table 2

1 Answers1