Latest record grouped by column

[seanconkie]

Hi - I have two tables, products and prices.  Each product can have multiple suppliers and the prices table contains a history of price by supplier over time.

In the products view, prices are included as a List column using ref_rows - but it returns all prices.  I'd like to only return each suppliers current (latest) price but can't work out how to do it.  I thought maybe a slice?

The kind of thing I'm thinking would be achievable in SQL like this:

select prices.supplier,
       prices.price,
       prices.product_id
  from prices 
 inner join (select supplier,
                    product_id,
                    max(date) date
               from prices
              group by supplier
                    product_id) current
    on (    prices.supplier   = current.supplier
        and prices.product_id = current.product_id
        and prices.date       = current.date) 
              

[Bethel_Vasto]

provided you have a date or datetime type column in your price table, you should be able to achieve this by creating a virtual column in your product table, that would have an app formula combining SELECT () and MAX()
something along the lines of 
SELECT(
Prices[Price],
AND([Supplier]=[_THISROW].[Supplier], MAX([Date])
)
)

be aware I am by no means an expert!

[SkrOYC]

Or maybe:

MAXROW(
 "Prices",
 "Date",
 [Supplier]=[_THISROW].[Supplier]
)

 Since it will return a value instead of a list (even if the list just have one value) and it's going to be the key column.

---

@seanconkie wrote:

prices are included as a List column using ref_rows - but it returns all prices.  I'd like to only return each suppliers current (latest) price

---

[seanconkie]

Thanks for the suggestions, I wasn't able to get this to work 😌 removed the price history as it wasn't core for the app purposes